Solved

limit to 1 row on the right side of left join

Posted on 2009-04-09
6
1,239 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

831 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