I think this is an easy query but I don't know how

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 2000 Forums

Transact-SQL (2000)

I think this is an easy query but I don't know how

Author

Topic

oepirobo
Starting Member

36 Posts

Posted - 2003-07-11 : 09:05:05

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

I posted this in the MSAccess thread but I also need to do this in SQL Server so I put it here too.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-11 : 09:37:29

Read up on UNION in Books Online.

Jonathan
{0}

oepirobo
Starting Member

36 Posts

Posted - 2003-07-11 : 09:46:15

Thanks, but that's not what I need.

I have to show it the way it is in my original post so I can't use UNION. The thing is that the tables don't have the same fields. I only used the ID fields in my example but in reality I need tree fields from the first table and four fields from the second table, and the field types are not the same.

Edited by - oepirobo on 07/11/2003 09:48:21

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-11 : 09:51:08

I don't think you've established a relationship between an entrance and an exit for a person. So unless you do that then, no.

PERSON table
PersonId
some other data

Entrance table
PersonId
EntranceId
some other data

Exit table
PersonId
ExitId
some other data

Does that look like what you have?

Post your DDL. Maybe we can come up with something.

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-11 : 10:14:13

quote:
Thanks, but that's not what I need.
I have to show it the way it is in my original post

Ummm-

select p.personID, e.entranceID, null as exitID
from person p
inner join entrance e on p.personID = e.personID

UNION

select p.personID, null, e.exitID
from person p
inner join exit e on p.personID = e.personID

Would give you the rowset you specified, assuming of course my guess at your table structure since it was not provided.

Jonathan
{0}

oepirobo
Starting Member

36 Posts

Posted - 2003-07-11 : 10:29:17

Yes sir. You're absolutely right.

Thanks for the help.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-11 : 10:39:20

I misread, I thought he wanted to relate an entrance to an exit

guess not

The union thig is what you've asked for in your sample result

Just like Jonathan has supplied to you..

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources