TSQL Problem - Link Table
source link: https://www.codesd.com/item/tsql-problem-link-table.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
TSQL Problem - Link Table
I am trying to do quite a straight forward TSQL link table but I am falling at the first hurdle, basically I want a two way link.
For example ....
If Contact 1 adds contact 2, then contact 2 is connected to contact 1 (and vice versa) the idea being that the query recognises they are paired up.
Table Example
RelatedDataID UniqueID Related_UniqueID
-------------------------------------------
1 AA BB
2 CC DD
3
Users Table
UserID UniqueID Username
----------------
1 AA Bob
2 BB Fred
3 CC Charlie
4 DD Billy
So basically when I run a query With UniqueID "AA" it will return
Username RelatedID
------------------------
Bob 1
Fred 1
Charlie 0
Billy 0
But also when I run it on UniqueID "CC" it should return
Username RelatedID
---------------------
Bob 0
Fred 0
Charlie 2
Billy 2
Does anyone know how I can achieve this? My current stored procedure seems to only bring back ones that are linked, and not the ones that are not linked. I need it to bring back a full list of all users, but to have the RelatedID come back as either a 0 or the RelatedDataID.
The UniqueID's are GUIDs.
Here my TSQL statement.
ALTER PROCEDURE sp_Test
@CompanyID int,
@UniqueID varchar(36),
@PersonTypeID int
AS
BEGIN
SET NOCOUNT ON;
SELECT
First_Name + ' ' + Last_Name AS Full_Name,
ISNULL(RelatedDataID,0) AS RelatedDataID
FROM
Users
LEFT JOIN
Related_Data
ON
Users.UniqueID = Related_Data.UniqueID
WHERE
Users.PersonTypeID = @PersonTypeID
AND
Users.Deleted = '0'
AND
((ISNULL(Related_Data.UniqueID,'') = '') OR (Related_Data.UniqueID = @UniqueID OR Related_Data.Related_UniqueID = @UniqueID))
(UPDATE) Given that you mentioned in comments that the UniqueId columns are guids and you want a zero to return when no relations, we have to case the guids to varchar. It is also not clear what the expected output should be if the a given user has multiple relations. This query will choose a relation on User.UniqueID = RelatedData.UniqueId over a relation on User.UniqueId = RelatedData.Related_UniqueId but will only return one row per user.
Select U.username
, U.FirstName + ' ' + U.LastName As Full_Name
, Case
When RD.Related_UniqueID Is Not Null Then RD.RelatedDataId
When RD1.UniqueId Is Not Null Then RD1.RelatedDataId
Else 0
End As RelatedID
From Users As U
Left Join Related_Data As RD
On RD.UniqueID = U.UniqueId
And (
RD.Related_UniqueID = @UniqueId
Or RD.UniqueId = @UniqueId
)
Left Join Related_Data As RD1
On RD1.Related_UniqueID = U.UniqueId
And RD1.UniqueID = @UniqueId
Where U.Deleted = '0'
And U.PersonTypeId = @PersonTypeId
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK