Solved

limit to 1 row on the right side of left join

Posted on 2009-04-09
6
1,222 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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 250 total points
Comment Utility
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
Comment Utility
yep, that's more what I was going towards.  thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now