Left Join with a subquery, possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Left Join with a subquery, possible?

Author

Topic

TempleClause
Starting Member

2 Posts

Posted - 2013-06-03 : 17:04:05

Hello,

I have these two queries:

SELECT Vehicle.VehicleId,VehicleAttribute.AttributeId
FROM Vehicle
LEFT JOIN VehicleAttribute
ON Vehicle.VehicleId = VehicleAttribute.VehicleId

SELECT UserAttribute.AttributeId
FROM UserAttribute
WHERE UserAttribute.UserId = '4D0F8AD2-7A4D-4E29-A6D3-E5FCD6075388'

I would now like to combine these two queries so I only the AttributeIds that are in the second query. I first tried it like this:

SELECT Vehicle.VehicleId,VehicleAttribute.AttributeId
FROM Vehicle
LEFT JOIN VehicleAttribute
ON Vehicle.VehicleId = VehicleAttribute.VehicleId
Where VehicleAttribute.AttributeId IN(
SELECT UserAttribute.AttributeId
FROM UserAttribute
WHERE UserAttribute.UserId = '4D0F8AD2-7A4D-4E29-A6D3-E5FCD6075388'
)

This works fine as long as the subquery returns at least on result but if it doesn't I don't get the VehicleId which I want to be returned regardless.

Hope you can help me out!

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-03 : 17:32:12

Try this

[CODE]
SELECT V.VehicleId,VA.AttributeId
FROM Vehicle V
LEFT JOIN VehicleAttribute VA
ON V.VehicleId = VA.VehicleId
OUTER APPLY
(SELECT U.AttributeId
FROM UserAttribute U
WHERE U.UserId = '4D0F8AD2-7A4D-4E29-A6D3-E5FCD6075388'
) A ON A. AttributeId =VA.AttributeId

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 23:47:20

quote:
Originally posted by MuMu88

Try this

[CODE]
SELECT V.VehicleId,VA.AttributeId
FROM Vehicle V
LEFT JOIN VehicleAttribute VA
ON V.VehicleId = VA.VehicleId
~~OUTER APPLY~~ LEFT JOIN
UserAttribute A ON A. AttributeId =VA.AttributeId
AND AUserId = '4D0F8AD2-7A4D-4E29-A6D3-E5FCD6075388'

[/CODE]

should be LEFT JOIN if you want to use ON condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

TempleClause
Starting Member

2 Posts

Posted - 2013-06-04 : 04:10:42

Thanks for your answers! You were almost right.

SELECT V.VehicleId,UA.AttributeId
FROM Vehicle V
LEFT JOIN VehicleAttribute VA
ON V.VehicleId = VA.VehicleId
LEFT JOIN UserAttribute  UA ON UA.AttributeId = VA.AttributeId  
AND UA.UserId = '4D0F8AD2-7A4D-4E29-A6D3-E5FCD6075388'

I just need to select UA.AttributeId instead of VA.AttributeId.

Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 04:15:20

quote:
Originally posted by TempleClause

Thanks for your answers! You were almost right.

SELECT V.VehicleId,UA.AttributeId
FROM Vehicle V
LEFT JOIN VehicleAttribute VA
ON V.VehicleId = VA.VehicleId
LEFT JOIN UserAttribute UA ON UA.AttributeId = VA.AttributeId
AND UA.UserId = '4D0F8AD2-7A4D-4E29-A6D3-E5FCD6075388'

I just need to select UA.AttributeId instead of VA.AttributeId.

Thanks a lot

Ok..That really depends on your requirement which we have no visibility on

Keep in mind that this will give you NULLs for AttributeId for all cases except for UserId = '4D0F8AD2-7A4D-4E29-A6D3-E5FCD6075388'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

zkwaywow
Starting Member

3 Posts

Posted - 2013-06-04 : 04:44:34

Great minds think alike.I agree with your though.

__________________
Living without an aim is like sailing without a compass.
unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources