CODEWITHSUNDEEP
mysqljoindatabase-designmultiple-tablesnormalize
Skyros
Skyros

Reputation: 37

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

Upvotes: 1

Views: 1540

Answers (2)

Dmitry Ovsyanko
Dmitry Ovsyanko

Reputation: 1416

SELECT
 books.title
 , location.locName
 , IFNULL(reservations.status, 0) status
FROM 
 books
 JOIN location 
 LEFT JOIN reservations ON (
  location.locID = reservations.locID
  AND books.bookID = location.bookID
 )
WHERE location.locID = 2

Upvotes: 0

Brian Driscoll
Brian Driscoll

Reputation: 19635

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

Upvotes: 3

Related Questions