Help writing SQL query using JOINS?

[menogeek]

I've simplified this so that someone can hopefully figure out how to do this. I have 4 tables: Person, Building, PersonRoom, and Room with the following relevant fields.

Person: PersonID

PersonRoom: PersonID, RoomID

Room: RoomID, RoomNumber

Building: BuildingID, BuildingName

I want to get all people and if they have a building and/or room number, I want that included in the row as well. The problem is that not every person has a location, and also some people might have multiple locations.

I have tried so many permutations of joins that I don't know where to begin any more.

If anyone has any ideas or can point me in the right direction, I would SO appreciate it!

Thanks!

 

[crufty]

This observation is based upon the data structure you presented and doesn't ultimately solve your problem but should get you going in the correct direction:

There isn't a connection between building and room, i.e.

person.personid -> personroom.personid
personroom.personroomid -> room.roomid
room.roomid !! building.buildingid

you need to have a link between room and building similar to your personroom mapping table.

 

[menogeek]

Sorry, there is in fact a connection between the Room and Building tables i.e. there is a RoomID field in the Building Table as well. I know the table structure is ok ... it's just that when I try to use an OUTER JOIN on PersonRoom.RoomID and Room.RoomID, I get an error because there's already an INNER JOIN on Room.RoomID and Building.RoomID. I don't know how to get around this.

Thanks again.

 

[tlbroadbent]

You can often overcome the problems of using INNER and OUTER JOINs in the query with proper grouping using parentheses.

Select p.PersonID, r.RoomNumber, b.BuildingName
From Person As P
Left Join (PersonRoom As pr
On p.PersonID=pr.PersonID
Inner Join (Room As r
On pr.RoomID=r.RoomID
Inner Join Building As b
On r.RoomID=b.RoomID))

Alternate solution: Use a sub-query

Select p.PersonID, q.RoomNumber, q.BuildingName
From Person As P
Left Join
(Select pr.PersonId, r.RoomNumber, b.BuildingName
From PersonRoom As pr
Inner Join Room r
On pr.RoomID=r.RoomID
Inner Join Building b
On r.RoomID=b.RoomID) As q
On p.PersonID=q.PersonID
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[stanleyrx]

I had to extract data that MAY be across 3 tables. I got rid of the unneeded code
so it may look choppy but the pricipal is here

SELECT A.ISBN, CATALOG_PRICE, FPT_COVER_PRICE
FROM table1 A, table2 B, table3 C
WHERE TITLE_STATUS_CODE NOT IN ('AFS', 'NOP') etc. etc.
UNION ALL --=====================================================
SELECT A.ISBN, CATALOG_PRICE, FPT_COVER_PRICE
FROM table1 A
WHERE TITLE_STATUS_CODE NOT IN ('AFS', 'NOP') etc. etc.
AND NOT EXISTS
(SELECT * FROM table2 B
WHERE A.PRODUCT_NUMBER = B.PRODUCT_NUMBER etc.etc.
UNION ALL --=====================================================
SELECT A.ISBN, CATALOG_PRICE, FPT_COVER_PRICE
FROM table1 A
WHERE TITLE_STATUS_CODE NOT IN ('AFS', 'NOP')
AND NOT EXISTS
(SELECT * FROM table3 C
WHERE A.ISBN = C.ISBN)
ORDER BY ISBN, TITLE_STATUS_CODE, TITL_STAT_EFF_DATE DESC