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.
| Author |
Topic |
|
nichoplast
Starting Member
1 Post |
 Posted - 2005-08-10 : 08:17:54
|
| 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 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-08-10 : 08:38:19
|
| This is a web site for questions about MS SQL Server, not MySQL.Also, since you have a query that returns the correct results, why are you posting a question at all?CODO ERGO SUM |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-10 : 08:51:00
|
| >>(I'm using PHP and MySQL) Post this question at MySQL forum of www.Dbforums.comMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Joining 3 tables