thefumbler
asked on
limit to 1 row on the right side of left join
My desired output is something like this, to show only one (any one) owner per project:
1 projectA Joe 1
2 projectB Sue 1
3 projectC Mike 1
4 projectD NULL NULL
5 projectE NULL NULL
Considering data from the Left side of a join:
SELECT ProjID, Descript FROM tmpProjects
1 projectA
2 projectB
3 projectC
4 projectD
5 projectE
NULL NULL
And a table from the right side of the join:
SELECT RecNo, ProjID, UserID, Owner FROM tmpAssigned
100 1 Joe 1
101 2 Sue 1
102 2 John 0
103 3 Mike 1
104 3 Katy 1
105 3 Jennifer NULL
106 4 NULL 0
The query below is very close :
SELECT dbo.tmpProjects.ProjID, dbo.tmpProjects.Descript, dbo.tmpAssigned.UserID, dbo.tmpAssigned.Owner FROM dbo.tmpProjects LEFT OUTER JOIN dbo.tmpAssigned ON dbo.tmpProjects.ProjID = dbo.tmpAssigned.ProjID AND dbo.tmpAssigned.Owner = 1
and results in this:
1 projectA Joe 1
2 projecB Sue 1
3 projectC Mike 1
3 projectC Katy 1
4 projectD NULL NULL
5 projectE NULL NULL
But it doesn't consider the fact that there could be more than one owner.
1 projectA Joe 1
2 projectB Sue 1
3 projectC Mike 1
4 projectD NULL NULL
5 projectE NULL NULL
Considering data from the Left side of a join:
SELECT ProjID, Descript FROM tmpProjects
1 projectA
2 projectB
3 projectC
4 projectD
5 projectE
NULL NULL
And a table from the right side of the join:
SELECT RecNo, ProjID, UserID, Owner FROM tmpAssigned
100 1 Joe 1
101 2 Sue 1
102 2 John 0
103 3 Mike 1
104 3 Katy 1
105 3 Jennifer NULL
106 4 NULL 0
The query below is very close :
SELECT dbo.tmpProjects.ProjID, dbo.tmpProjects.Descript, dbo.tmpAssigned.UserID, dbo.tmpAssigned.Owner FROM dbo.tmpProjects LEFT OUTER JOIN dbo.tmpAssigned ON dbo.tmpProjects.ProjID = dbo.tmpAssigned.ProjID AND dbo.tmpAssigned.Owner = 1
and results in this:
1 projectA Joe 1
2 projecB Sue 1
3 projectC Mike 1
3 projectC Katy 1
4 projectD NULL NULL
5 projectE NULL NULL
But it doesn't consider the fact that there could be more than one owner.
sorry missed something
SELECT dbo.tmpProjects.ProjID, min(dbo.tmpProjects.Descript), min(dbo.tmpAssigned.UserID), min(dbo.tmpAssigned.Owner) FROM dbo.tmpProjects
LEFT OUTER JOIN dbo.tmpAssigned ON dbo.tmpProjects.ProjID = dbo.tmpAssigned.ProjID AND dbo.tmpAssigned.Owner = 1
group by dbo.tmpProjects.ProjID
ASKER
thanks ralmada...technically that works, but I guess I didn't explain thoroughly. I'm trying to add all of the right side of the LEFT OUTER JOIN join to a much larger more complicated query with a lot of joins without disturbing the existing query, at least as little as possible. So I hope to avoid group by types of changes.
Is there a way to do this without altering the left side with a group by syntax?
Is there a way to do this without altering the left side with a group by syntax?
You can try this:
SELECT
dbo.tmpProjects.ProjID,
dbo.tmpProjects.Descript,
a.UserID,
a.Owner
FROM dbo.tmpProjects
LEFT OUTER JOIN (select ProjID, min(UserID), min(Owner) from dbo.tmpAssigned group by ProjID) a
ON dbo.tmpProjects.ProjID = a.ProjID AND a.Owner = 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yep, that's more what I was going towards. thanks!
Open in new window