CODEWITHSUNDEEP
sql-serverrelationship
Pegaz
Pegaz

Reputation: 377

Get results from different tables based on relationship table

Continuing from my previous post:

Get results from multiple tables based on relationship table

See fiddle for what I'm trying to accomplish:

http://sqlfiddle.com/#!6/657f3/3

I have two additional tables (Organizations and Groups) that have relationship to the User table. The only difference is that the relationship is defined the other way in the relationship table.

What I want is a single row for each user with the additional columns taken from the other tables.

What I'm getting now:

Id  Name    PhoneNumber Organization    GroupName
1   John    1234    Organization1   (null)
1   John    1234    (null)  Group1
2   Mary    5555    Organization2   (null)
3   Michael (null)  Organization2   (null)
3   Michael (null)  (null)  Group2

What I want:

Id  Name    PhoneNumber Organization    GroupName
1   John    1234    Organization1   Group1
2   Mary    5555    Organization2   (null)
3   Michael (null)  Organization2   Group2

Could you help me understand what is happening here and how could I solve this?

Upvotes: 0

Views: 33

Answers (1)

TriV
TriV

Reputation: 5148

I saw your example and maybe you need a group by

SELECT u.Id
    ,u.Name
    ,max(p.PhoneNumber) AS PhoneNumber
    ,Max(c.Name) as Organization
    ,Max(g.Name) as GroupName
FROM Users u
LEFT JOIN Relationship r ON r.Id = u.Id
LEFT JOIN Phone p ON p.Id = r.ChildId
LEFT JOIN Relationship rr ON rr.ChildId = u.Id
LEFT JOIN Organizations c ON c.Id = rr.Id
LEFT JOIN Groups g ON g.Id = rr.Id
GROUP BY u.Id
         ,u.Name

Upvotes: 1

Related Questions