How would I set this up if there were multiple authors or publishers to one book?
Thanks
]]>The JOIN statement creates a complex statement that builds on previous components. If you look at the first join (between author and book) you now have effectively a meta-table that includes fields from both. The next join needs only to reference fields from the meta-table, which in this case includes fields from book as well as author.
This is no to say that you couldn’t restructure the query to match your supposition, however you’d need to think carefully about the direction (LEFT/RIGHT) of the join as I’m using LEFT JOIN knowing that it will always provide me with a result provided there is a valid author, regardless of if that author has a book or publisher. If I structured the query such that book was before the FROM then I would need a valid book to list an author.
Note that I’m using the term meta-table as a convenience. The underlying database doesn’t need to create a separate structure.
]]>in the previous episode to this, a SELECT query was made with 3 joins, namely:
SELECT `title`,
CONCAT_WS(’ ‘,`author`.`first_name`,`author`.`last_name`) AS `author`,
`publisher`.`name` AS `publisher`,
`format`.`name` as `format`,
`price`, `isbn` FROM `book`
JOIN `author` ON `book`.`author` = `author`.`id`
JOIN `publisher` ON `book`.`publisher` = `publisher`.`id`
JOIN `format` ON `book`.`format` = `format`.`id`;
As I understand it, the tables `author`, `publisher`, and `format` each have a separate link back to the book table. Is that correct? Now in this episode you have the query:
SELECT CONCAT_WS(’ ‘,`author`.`first_name`,`author`.`last_name`) AS `author`,
`book`.`title` FROM `author`
LEFT JOIN `book` ON `author`.`id` = `book`.`author`
LEFT JOIN `publisher` ON `publisher`.`id` = `book`.`publisher`
WHERE `publisher`.`name` = ‘Bantam’;
I can see in the first join that the `book` table is linked back to the `author` table. But the second join to `publisher` is a join to the `book` table. I the thought that with multiple joins all tables following JOIN statements had to link back to the initital table referred to in the FROM statement.
]]>Boel
]]>