Question on multiple joins - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Question on multiple joins

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-04-24 : 10:58:02

I have a table called asset_owner which stores a serial number of an asset and an owner's name. I also have 2 additional tables called related_owner and related_user. How can I join the owner's name from asset_owner to related_owner if a match exist but use related_user if a match doesn't?
I want my output to have 2 columns...serialnumber and owner name.

Any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-24 : 11:08:25

Would need to join the three tables, and use coalesce:

SELECT
	ao.serialnumber,
	COALESCE(ao.owner_name,ro.name,ru.name) AS OwnerName
FROM
	asset_owner ao
	LEFT JOIN related_owner ro ON ro.serialnumber = ao.serialnumber
	LEFT JOIN related_user ru ON ru.serialnumber = ao.serialnumber;

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-27 : 06:25:42

If Sunita's Query is not what you are looking for then please be more elaborate about your requirement.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources