CODEWITHSUNDEEP
mysqlsql
threedollarbill
threedollarbill

Reputation: 165

MySQL conditional LEFT JOIN?

I have three tables: albums, songs, and images. The tables look something like this:

albums table:

id   |     title     |   image_id
5    |  First Album  |      1
6    | Another Album |      2

songs table:

id    |   album_id   |     title     |   image_id
32    |      5       |    My Song    |      3
33    |      5       |  Another One  |      4
34    |      5       |   First Song  |      0
35    |      6       |   My Song #2  |      0
36    |      6       |  Fancy Title  |      0
37    |      6       |  My Love Song |      5

images table:

id   |     path
1    | path/to/image1.jpg
2    | path/to/image2.jpg
3    | path/to/image3.jpg
4    | path/to/image4.jpg
5    | path/to/image5.jpg

I am trying to display a list of songs with their titles and corresponding images.

If the songs don't have an image (if the image_id column is 0), then I want to just use the album image.

The only code I have so far is this, and I am stuck:

SELECT songs.title, images.path
FROM songs
LEFT JOIN images
ON images.id = songs.image_id

// ...if songs.image_id is 0, i want to just use the image_id of the corresponding album instead

It would be nice if it was an efficient query that could work with large tables.

Upvotes: 4

Views: 1040

Answers (3)

Dylan Su
Dylan Su

Reputation: 6065

Here is a solution using multiple LEFT JOINs, with demo using data you provided.

The final query:

SELECT 
    s.title, 
    COALESCE(i1.path, i2.path)
FROM songs s
LEFT JOIN albums a ON s.album_id = a.id
LEFT JOIN images i1 ON s.image_id = i1.id
LEFT JOIN images i2 ON a.image_id = i2.id
ORDER BY s.id;

Below is a full demo.

SQL:

-- Data for demo
create table albums(id int, title char(100), image_id int);
insert into albums values
(5    ,  'First Album'  ,      1),
(6    , 'Another Album' ,      2);

create table songs(id int, album_id int, title char(100), image_id int);
insert into songs values
(32    ,      5       ,    'My Song'    ,      3),
(33    ,      5       ,  'Another One'  ,      4),
(34    ,      5       ,   'First Song'  ,      0),
(35    ,      6       ,   'My Song #2'  ,      0),
(36    ,      6       ,  'Fancy Title'  ,      0),
(37    ,      6       ,  'My Love Song' ,      5);

create table images(id int, path char(200));
insert into images values
(1    , 'path/to/image1.jpg'),
(2    , 'path/to/image2.jpg'),
(3    , 'path/to/image3.jpg'),
(4    , 'path/to/image4.jpg'),
(5    , 'path/to/image5.jpg');

SELECT * FROM albums;
SELECT * FROM songs;
SELECT * FROM images;

-- SQL needed 
SELECT 
    s.title, 
    COALESCE(i1.path, i2.path)
FROM songs s
LEFT JOIN albums a ON s.album_id = a.id
LEFT JOIN images i1 ON s.image_id = i1.id
LEFT JOIN images i2 ON a.image_id = i2.id
ORDER BY s.id;

Output:

mysql> SELECT
    -> s.title,
    -> COALESCE(i1.path, i2.path)
    -> FROM songs s
    -> LEFT JOIN albums a ON s.album_id = a.id
    -> LEFT JOIN images i1 ON s.image_id = i1.id
    -> LEFT JOIN images i2 ON a.image_id = i2.id
    -> ORDER BY s.id;
+--------------+----------------------------+
| title        | COALESCE(i1.path, i2.path) |
+--------------+----------------------------+
| My Song      | path/to/image3.jpg         |
| Another One  | path/to/image4.jpg         |
| First Song   | path/to/image1.jpg         |
| My Song #2   | path/to/image2.jpg         |
| Fancy Title  | path/to/image2.jpg         |
| My Love Song | path/to/image5.jpg         |
+--------------+----------------------------+
6 rows in set (0.00 sec)

Upvotes: 2

Ema
Ema

Reputation: 21

You can also use UNION in this way:

SELECT songs.title, images.path
FROM songs
LEFT JOIN images ON (images.id = songs.image_id)
WHERE songs.image_id != 0
UNION
SELECT songs.title, images.path
FROM songs
LEFT JOIN albums ON (songs.album_id = albums.id)
LEFT JOIN images ON (images.id = albums.image_id)
WHERE albums.image_id != 0 AND songs.image_id = 0

Basically you first select all the songs with an image, then append all the songs with an album image if the song itself doesn't have it.

Upvotes: 1

Nathaniel Ford
Nathaniel Ford

Reputation: 21269

Something like this should work:

SELECT s.title, COALESCE(si.path, ai.path)
FROM albums AS a
INNER JOIN songs s ON a.id = s.album_id
LEFT JOIN images ai ON i.id = a.image_id
LEFT JOIN images si ON i.id = s.image_id

Note that you have to join the images table to the albums table AND to the songs table. ai and si serve as separate namespacing for these joins so that COALESCE knows how to find options to choose from.

(Note that the INNER JOIN is debatable depending on how you expect your data to look.)

Upvotes: 3

Related Questions