Skyros Skyros - 1 year ago 48
MySQL Question

Joining multiple tables

I'm rather new to normalizing tables and I'm having some troubles wrapping my head around getting the correct information out of 3 tables. I made an example involving reserving books out of different libraries. I have 3 tables. Books, locations, reservations (listed below):

//SQL query:
$sql =
"SELECT * FROM books
JOIN (location LEFT JOIN reservations ON location.locID = reservations.locID)
ON books.bookID = location.bookID
WHERE location.locID=2
";


and the output I was hoping to achieve if I were to list the books in Campus B:

title |locName |status
Book 1|Campus B|1
Book 2|Campus B|0
Book 3|Campus B|0
Book 4|Campus B|0
Book 5|Campus B|1


For some reason I'm definitely not getting the output I think I should, and I was curious if anyone has some suggestions. I'm sure once I see whats going on I'll understand what I did incorrectly.

table: books
bookID|title
1 | Book 1
2 | Book 2
3 | Book 3
4 | Book 4
5 | Book 5


table: location
locID|locName
1 | campus A
2 | campus B
3 | campus C


table: reservations
bookID|locID|status
1 | 1 | 1
3 | 1 | 1
4 | 1 | 1
1 | 2 | 1
5 | 2 | 1
4 | 3 | 1
5 | 3 | 1

Answer Source

I think this is more in line with what you're looking for:

SELECT *
FROM books b
LEFT JOIN reservations r ON b.bookID = r.bookID
JOIN location l on r.locID = l.locID
WHERE l.locID = 2

This will return a list of the books reserved at the location with locID=2.

In this case I have a LEFT JOIN to preserve your original query, however given your WHERE clause any records with NULL in the location.locID field will not be selected.

Therefore, I could re-write your query with all INNER joins, like so:

SELECT *
FROM books b
JOIN reservations r ON b.bookID = r.bookID
JOIN location l on r.locID = l.locID
WHERE l.locID = 2

Other queries that you might find interesting:

Get ALL books, regardless of whether or not they are reserved anywhere:

SELECT *
FROM books b
LEFT JOIN reservations r ON b.bookID = r.bookID
JOIN location l on r.locID = l.locID

Get ALL locations, regardless of whether or not there are books reserved there:

SELECT *
FROM books b
JOIN reservations r ON b.bookID = r.bookID
RIGHT JOIN location l on r.locID = l.locID

Get ALL books and ALL locations:

SELECT *
FROM books b
LEFT JOIN reservations r ON b.bookID = r.bookID
RIGHT JOIN location l on r.locID = l.locID
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download