Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1294
  • Last Modified:

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.  
0
thefumbler
Asked:
thefumbler
  • 4
  • 2
1 Solution
 
ralmadaCommented:
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
 
ralmadaCommented:
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
 
thefumblerAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ralmadaCommented:
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
 
ralmadaCommented:
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
 
thefumblerAuthor Commented:
yep, that's more what I was going towards.  thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now