MySQL: List data with its related data from other tables if available

Question

Here is my scenario:

Table image_book_rel contains the relationship between two tables, namely book_table and image_table.

I need to get the list of books from table book_table and the related image data from table image_table if available.

Here's roughly how the tables look like (simplified):

/* book_table */
id       title      price
-------- ---------- ------
1        Book 1     $10
2        Book 2     $13
3        Book 3     $15
4        Book 4     $20
5        Book 5     $12
6        Book 6     $10
7        Book 7     $14

/* image_table */
id       description      file
-------- ---------------- -------------
20       Image of Book 2  book_img2.jpg
30       Image of Book 3  book_img3.jpg
50                        book_img5.jpg
70       Image of Book 7  book_img7.jpg

/* image_book_rel */
id       book_id   image_id
-------- --------- ---------
1        2         20
2        3         30
3        5         50
4        7         70

And here's what I need for my query result:

/* Expected result: */
book_id  image_id  filename        book_title  img_desc
-------- --------- --------------- ----------- -----------------
1                                  Book 1
2        20        book_img2.jpg   Book 2       Image of Book 2
3        30        book_img3.jpg   Book 3       Image of Book 3
4                                  Book 4
5        50        book_img5.jpg   Book 5
6                                  Book 6
7        70        book_img7.jpg   Book 7       Image of Book 7

Here's what I have tried:

SELECT bk.id AS book_id, im.id AS image_id, im.file AS filename, bk.title AS book_title, im.description AS img_desc
    FROM book_table AS bk
        LEFT JOIN image_book_rel AS bi
            ON bk.id = bi.book_id
        LEFT JOIN image_table AS im
            ON bi.image_id = im.id
    WHERE bk.id = bk.id
        AND bi.book_id = bk.id
        AND bi.image_id = im.id
    ORDER BY bk.id

The result from the query above retrieves ONLY the books that have images. I need to retrieve ALL books. How do I go about this?

Answer 1

You are mixing two types of JOIN sintaxes. The WHERE clause you have is duplicating the LEFT JOIN clauses (which are what you want), but it is equivalent to implementing an INNER JOIN, which excludes the rows with no images.

The correct query is much simpler:

SELECT bk.id AS book_id, im.id AS image_id, im.file AS filename, bk.title AS book_title, im.description AS img_desc
    FROM book_table AS bk
        LEFT JOIN image_book_rel AS bi
            ON bk.id = bi.book_id
        LEFT JOIN image_table AS im
            ON bi.image_id = im.id
    ORDER BY bk.id

To further refine the answer... the WHERE condition

bk.id = bk.id

is absolutely useless, whereas the WHERE conditions:

bi.book_id = bk.id
bi.image_id = im.id

are invalidating the LEFT JOINS you have specificied, by filtering out the rows that have a NULL im.id. Those conditions force the same result as would the query:

SELECT bk.id AS book_id, im.id AS image_id, im.file AS filename, bk.title AS book_title, im.description AS img_desc
    FROM book_table AS bk
        JOIN image_book_rel AS bi
            ON bk.id = bi.book_id
        JOIN image_table AS im
            ON bi.image_id = im.id
    ORDER BY bk.id

Answer 2

Try it without WHERE and use IFNULL to replace null values with empty string:

SELECT IFNULL(bk.id,'') AS book_id, IFNULL(im.id,'') AS image_id, IFNULL(im.file,'') AS filename, IFNULL(bk.title,'') AS book_title, IFNULL(im.description,'') AS img_desc
    FROM book_table AS bk
        LEFT JOIN image_book_rel AS bi
            ON bk.id = bi.book_id
        LEFT JOIN image_table AS im
            ON bi.image_id = im.id
    ORDER BY bk.id

Result:

BOOK_ID   IMAGE_ID  FILENAME        BOOK_TITLE   IMG_DESC
1                                   Book 1       
2         20        book_img2.jpg   Book 2       Image of Book 2
3         30        book_img3.jpg   Book 3       Image of Book 3
4                                   Book 4       
5         50        book_img5.jpg   Book 5       
6                                   Book 6       
7         70        book_img7.jpg   Book 7       Image of Book 7

See result in SQL Fiddle.