SQL Join problems

SQL, Join, translation table, MySQL

[nichoplast]

Hi all!

I have a problem joining three tables. My tables are:
rooms: room_id ¦ room_name
computers: computer_id ¦ computer_name ¦ room_id
bookings: booking_id ¦ computer_id ¦ date

I want to join them so that i get a listing that displays all room names, and if there is a booking for any computer in that room I also want to display the computer name and the booking date next to the room name. If one room has bookings for several computers I would like to display that room several times.

I have gotten a correct result with:

SELECT rooms.room_name, t.computer_name, temp.date FROM rooms LEFT JOIN (SELECT computers.room_id, computers.computer_name, bookings.date FROM computers JOIN bookings ON (bookings.computer_id = computers.computer_id)) AS temp ON (rooms.room_id = temp.room_id)

but I would like to use something shorter like:
SELECT rooms.room_name, computers.computer_name, bookings.date from computers INNER JOIN bookings ON (computers.computer_id = bookings.computer_id) RIGHT JOIN rooms ON (computers.room_ID = rooms.room_ID)

That however gives me a list where every combination of rooms and computers is listed and bookings displayed when there is a match. I don't really get why this doesn't work and I've been staring at it for days. Could somebody help me out? (I'm using PHP and MySQL)

Thanks,
Nick

[coopster]

Welcome to WebmasterWorld, nichoplast.

The easiest way to develop an SQL statement is by doing exactly what you have done so far, write it out in plain terminology. Then apply your SQL to the plain terms. Let's take your verbiage and put it all together:

I want to join them so that i get a listing that displays all room names, and if there is a booking for any computer in that room I also want to display the computer name and the booking date next to the room name. If one room has bookings for several computers I would like to display that room several times.

So, we want the room names, computer name and booking date.

SELECT 
 rooms.room_name, 
 computers.computer_name 
 bookings.date

I want to join them so that i get a listing that displays all room names, and if there is a booking for any computer in that room I also want to display the computer name and the booking date next to the room name. If one room has bookings for several computers I would like to display that room several times.

The only way to get them all is with a LEFT JOIN. If there is no match in the right hand table (computers) then the computer_name column will return NULL.

SELECT 
 rooms.room_name, 
 computers.computer_name, 
 bookings.date 
 FROM rooms 
 LEFT JOIN computers ON (rooms.room_id = computers.room_id)

I want to join them so that i get a listing that displays all room names, and if there is a booking for any computer in that room I also want to display the computer name and the booking date next to the room name. If one room has bookings for several computers I would like to display that room several times.

An INNER JOIN is what we need here. An INNER JOIN is a regular join that says give me everything from the table on the left that has a matching record in the table on the right. The table on the left in this case is going to be the computers table.

SELECT 
 rooms.room_name, 
 computers.computer_name, 
 bookings.date 
 FROM rooms 
 LEFT JOIN computers ON (rooms.room_id = computers.room_id) 
 INNER JOIN bookings ON (computers.computer_id = bookings.computer_id)

I want to join them so that i get a listing that displays all room names, and if there is a booking for any computer in that room I also want to display the computer name and the booking date next to the room name. If one room has bookings for several computers I would like to display that room several times.

This is going to happen for us by sheer nature of the JOIN in our statement. However, we usually like to see them together. This is where we can bring them back in a certain order, room in this case. The formatting part will be handled in your application/script.

SELECT 
 rooms.room_name, 
 computers.computer_name, 
 bookings.date 
 FROM rooms 
 LEFT JOIN computers ON (rooms.room_id = computers.room_id) 
 INNER JOIN bookings ON (computers.computer_id = bookings.computer_id) 
 ORDER BY rooms.room_name 
;

Hopefully I have understood you correctly. If not, absorb what we have here and post your success/failures and we'll help you get there ;)

[nichoplast]

Thanks for your answer and the explanations about join! It helped me understand a whole lot more about joins. I still don't seem to get the right answer however. The query outputs a lis where only the rooms with bookings appear. What seems to happen is that the inner join is performed last eliminating the results from the left join. Is there any way to tell mysql in which order to do these tasks? (if that is the problem)

Once again, heaps of thanks for your help and time,
Nick

[nichoplast]

I've figured it out now... There's a bug in MySQL that doesn't let an inner join be followed by a right join.
[bugs.mysql.com...]

Thanks for your help!
/Nick

[coopster]

But we didn't use a RIGHT JOIN ... doesn't make sense. This bug shouldn't apply the query here. I use LEFT JOINS and INNER JOINS quite often.

[coopster]

And I screwed this one up, sorry, nichoplast. I even said it one paragraph before my mistake! This is the incorrect part ...

---

I want to join them so that i get a listing that displays all room names, and if there is a booking for any computer in that room I also want to display the computer name and the booking date next to the room name. If one room has bookings for several computers I would like to display that room several times.

An INNER JOIN is what we need here. An INNER JOIN is a regular join that says give me everything from the table on the left that has a matching record in the table on the right. The table on the left in this case is going to be the computers table.

SELECT 
 rooms.room_name, 
 computers.computer_name, 
 bookings.date 
 FROM rooms 
 LEFT JOIN computers ON (rooms.room_id = computers.room_id) 
 INNER JOIN bookings ON (computers.computer_id = bookings.computer_id)

---

... because as I stated, if you want all rows returned we need to use a LEFT JOIN!

SELECT  
 rooms.room_name,  
 computers.computer_name,  
 bookings.date  
 FROM rooms  
 LEFT JOIN computers ON (rooms.room_id = computers.room_id)  
 LEFT JOIN bookings ON (computers.computer_id = bookings.computer_id)  
 ORDER BY rooms.room_name, bookings.date, computers.computer_name 
;

I also added to your ORDER BY clause which I figured you would be doing anyway, but threw it down here for demonstration.

Now, when you loop through the result set, any room without computers in it will return a NULL computer_name and any room that doesn't have a booking for a computer in that room will have a NULL booking date.

Sorry for the incorrect direction there -- I'm surprised nobody else caught that ;)