Need help with a query - 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

Other Forums

MS Access

Need help with a query

Author

Topic

oepirobo
Starting Member

36 Posts

Posted - 2003-07-11 : 09:01:52

Hi. I want to do a query from 3 tables: Persons, Entrances and Exits. You may have guessed this: a person has many entrances and also many exits. I want my query to return a table like this

PersonID EntranceID ExitID
1            45            null
1            55            null
1            null           101
1            null           105

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-07-11 : 12:17:26

Assuming the PersonID is the foreign key in the entrances and exits tables....

select p.PersonID, en.EntranceID, null as ExitID
from Persons p left join Entrances en on p.PersonID = en.PersonID
union
select p.PersonID, null, ex.ExitID
from Persons p left join Exits ex on p.PersonID = ex.PersonID

hth,
Justin

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"

Andy Verity
Starting Member

12 Posts

Posted - 2003-07-15 : 06:31:16

Just out of curiosity why do you want the entrances and exits listed like this?

quote:

Hi. I want to do a query from 3 tables: Persons, Entrances and Exits. You may have guessed this: a person has many entrances and also many exits. I want my query to return a table like this

PersonID EntranceID ExitID
1            45            null
1            55            null
1            null           101
1            null           105

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources