Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

limit to 1 row on the right side of left join

Posted on 2009-04-09
6
Medium Priority
?
1,283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 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