Select A and either B or C - 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)

Select A and either B or C

Author

Topic

sqlmuppet
Starting Member

2 Posts

Posted - 2009-11-24 : 06:56:51

I have three tables: Person, LocalAddress, NonLocalAddress

I want to select a list of Persons and their address. They will have EITHER a LocalAddress or NonLocalAddress, but not BOTH.

Any clues on how I'd code this?

Here's the two separate SELECTs I want to combine:

SELECT Person.name, Person.id
from Person

(so for each Person above I want the following....)

select addressline, id
from LocalAddress JOIN Person
where LocalAddress.id = Person.id
union all
select addressline, id
from NonLocalAddress, Person
where NonLocalAddress.id = Person.id

Thanks in advance!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-24 : 07:29:12

[code]
select
Person.name,
Person.id,
isnull(local.addressline,nonlocal.addressline) as addressline
from Person
left join LocalAddress as local
on local.id = Person.id
left join NonLocalAddress as nonlocal
on nonlocal.id = Person.id
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.