Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

limit to 1 row on the right side of left join

Posted on 2009-04-09
6
Medium Priority
?
1,291 Views
Last Modified: 2012-05-06
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.  
0
Comment
Question by:thefumbler
  • 4
  • 2
6 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24112649
If it doesn't matter who's the owner.
SELECT     dbo.tmpProjects.ProjID, min(dbo.tmpProjects.Descript), min(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
group by dbo.tmpProjects.ProjID

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24112652
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

Open in new window

0
 
LVL 1

Author Comment

by:thefumbler
ID: 24112680
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?

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Expert Comment

by:ralmada
ID: 24112694
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

Open in new window

0
 
LVL 41

Accepted Solution

by:
ralmada earned 1000 total points
ID: 24112699
I mean
SELECT     
	dbo.tmpProjects.ProjID, 
	dbo.tmpProjects.Descript, 
	a.UserID, 
	a.Owner 
FROM         dbo.tmpProjects 
LEFT OUTER JOIN (select ProjID, min(UserID) as UserID, min(Owner) as Owner from dbo.tmpAssigned group by ProjID) a
ON dbo.tmpProjects.ProjID = a.ProjID AND a.Owner = 1

Open in new window

0
 
LVL 1

Author Comment

by:thefumbler
ID: 24112708
yep, that's more what I was going towards.  thanks!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question