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)开发者_如何学JAVA,
@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
Just move the UniqueID (filter/where) condition into the LEFT JOIN condition
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
AND
((ISNULL(Related_Data.UniqueID,'') = '') OR (Related_Data.UniqueID = @UniqueID OR Related_Data.Related_UniqueID = @UniqueID))
WHERE
Users.PersonTypeID = @PersonTypeID
AND
Users.Deleted = '0'
END
精彩评论