I have a problem with a mysql join i'm trying to complete. This is my code:
SELECT title, books.`author_id`, books.`publisher_id`, books.`format_id`, books.`genre_id`, pages, isbn, description, DATE_FORMAT(release_date, '%M, %d, %Y')
FROM `books`
INNER JOIN `authors` ON (`authors`.`author_id`= `books`.`author_id`)
INNER JOIN `publishers` ON (`publishers`.`publisher_id`= `books`.`publisher_id`)
INNER JOIN `formats` ON (`formats`.`format_id`= `books`.`format_id`)
INNER JOIN `genres` ON (`genres`.`genre_id`= `books`.`genre_id`)`
authors, publishers, formats and genres are all separate tables each with two columns. author_id and author in the case of the authors table, and the same format for the others.
The join doesn't seem to work no matter what i do, please help me to see where i've gone wrong.
The problem is in the select part of your query.
ReplyDeleteSELECT title, books.`author_id`, books.`publisher_id`, books.`format_id`, books.`genre_id`
You are selecting the author_id, publisher_id, format_id and genre_id from the books table, and nothing from the joined tables, so it is no wonder that you are missing the values you want.
SELECT title, books.`author_id`, authors.`author`, books.`publisher_id`, publishers.`publisher`, books.`format_id`, formats.`format`, books.`genre_id`, genres.`genre`
This will produce the values you want in addition to the id values.
I think that once you've started to use escaped column names you should stick with it for whole query. The same for using prefixed column names (with table name).
ReplyDeleteThe second thing is release date, you should declare name manually for it with DATE_FORMAT(release_date, '%M, %d, %Y') AS release_date (formatting date should be matter of presentation layer not model (getting from db) so I'd be better to format date with date() when outputing it).
Anyway your query should look like this:
SELECT books.`title`, books.`author_id`, books.`publisher_id`, books.`format_id`,
books.`genre_id`, books.`pages`, books.`isbn`, books.`description`,
DATE_FORMAT(books.`release_date`, '%M, %d, %Y') AS `release_date`,
-- Based on your commend I assume you want to use this:
authors.`name` as `author_name` -- ...
FROM `books`
INNER JOIN `authors` ON (`authors`.`author_id`= `books`.`author_id`)
INNER JOIN `publishers` ON (`publishers`.`publisher_id`= `books`.`publisher_id`)
INNER JOIN `formats` ON (`formats`.`format_id`= `books`.`format_id`)
INNER JOIN `genres` ON (`genres`.`genre_id`= `books`.`genre_id`)`