conditional mysql query multiple tables

Question

I have 2 tables in mysql database as shown below. I am looking for a query that will select * from books but if preview_image = 'none' then preview_image = the hash_id of the row with the largest size where books.id = images.parentid. Hope this makes sense.

table books
+----------------+---------------+
| id    | title  | preview_image |
+----------------+---------------+
| 1     | book1  | 55859076d906  |
| 2     | book2  | 20a14f9fd7cf  |
| 3     | book3  | none          |
| 4     | book4  | ce805ecff5c9  |
| 5     | book5  | e60a7217b3e2  |
+----------------+---------------+

table images
+-------------+------+---------------+
| parentid    | size | hash_id       |
+--------------------+---------------+
| 2           | 100  | 55859076d906  |
| 1           | 200  | 20a14f9fd7cf  |
| 3           | 300  | 34805fr5c9e5  |
| 3           | 400  | ce805ecff5c9  |
| 3           | 500  | e60a7217b3e2  |
+--------------------+---------------+

Thanks

I haven't really tried anything yet. I am not familiar with conditional queries. I believe I would also need a subselect. Maybe something like: select * from books if(books.preview_image = 'none' , select hash_id from images where images.parentid =books.id order by size desc limit 0,1)) something like this. I am not sure tho. — altair909, Jan 28 '16 at 18:36

score 0 · Answer 1 · answered Jan 28 '16 at 18:29

You can use SUBSTRING_INDEX() to obtain the first record from a sorted GROUP_CONCAT(), and switch using a CASE expression:

SELECT   books.id, books.title, CASE books.preview_image
           WHEN 'none' THEN SUBSTRING_INDEX(
             GROUP_CONCAT(images.hash_id ORDER BY images.size DESC SEPARATOR ',')
           , ',', 1)
           ELSE books.preview_image
         END AS preview_image
FROM     books LEFT JOIN images ON images.parentid = books.id
GROUP BY books.id

score 0 · Answer 2 · edited May 23 '17 at 12:30

Write a subquery that finds the desired hash ID for each parent ID, using one of the techniques in SQL Select only rows with Max Value on a Column. Then join this with the books table.

SELECT b.id, b.title, IF(b.preview_image = 'none', i.hash_id, b.preview_image) AS image
FROM books AS b
LEFT JOIN (SELECT i1.parentid, i1.hash_id
           FROM images AS i1
           JOIN (SELECT parentid, MAX(size) AS maxsize
                 FROM images
                 GROUP BY parentid) AS i2
           ON i1.parentid = i2.parentid AND i1.size = i2.size) AS i
ON b.id = i.parentid

conditional mysql query multiple tables

2 Answers2