Q:

Select random image cover in SQL

I would like to select a random image for cover of the album.  If the album contains images tagged with "cover", then it should select from those images only.  If a sub-album has images tagged with "cover", then it should not select images that are not tagged with "cover". 
Here is a visual structure. 
Album 1
├───Image 1
├───Album 2
│   ├───Image 2
│   ├───Image 3 (cover)
│   └───Image 4 (cover)
└───Album 3
    ├───Image 5
    └───Image 6

Album 1 should choose from Image 1, Image 3, Image 4, Image 5, Image 6
Album 2 should choose from Image 3, Image 4
Album 3 should choose from Image 5, Image 6

Here is my tables
Table tags
| id | name  |
|----|-------|
| 1  | cover |

Table image_tag
| id | image_id | tag_id |
|----|----------|--------|
| 1  | 3        | 1      |
| 2  | 4        | 1      |

Table album_image
| id | album_id | image_id |
|----|----------|----------|
| 1  | 1        | 1        |
| 2  | 2        | 2        |
| 3  | 2        | 3        |
| 4  | 2        | 4        |
| 5  | 3        | 5        |
| 6  | 3        | 6        |

Table albums
| id | name    | parent_id |
|----|---------|-----------|
| 1  | album 1 | null      |
| 2  | album 2 | 1,        |
| 3  | album 3 | 1,        |

This is the SQL I came up. 
SELECT ai.image_id AS image_id FROM albums AS a
INNER JOIN album_image AS ai ON ai.album_id = a.id
LEFT JOIN image_tag AS it ON it.image_id = ai.image_id AND it.tag_id = 1[1]
WHERE a.id = 1[2] OR parent_id LIKE '1,%'[3]
ORDER BY CASE
    WHEN it.tag_id IS NOT NULL AND a.id = 1[4] THEN RAND()
    ELSE RAND() + 1
END  ASC LIMIT 1;

[1]: This is current get by another SQL in PHP.  It would be nice if the can be merged.  Is it possible to LEFT JOIN a INNER JOIN table?  What if the tag does not exist? 
[2], [4]: This is the album id came from PHP. 
[3]: This is came from PHP to ensure sub-album is included. 
The solution will generate the following result. 

Album 1 should choose from Image 1, Image 2, Image 3, Image 4, Image 5, Image 6
Album 2 should choose from Image 3, Image 4
Album 3 should choose from Image 5, Image 6

Is there a way to modify the SQL so that I can generate my expected result? 
P.S. I am running MySQL. 
EDIT
Schema for testing
CREATE TABLE tags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name TEXT NOT NULL
);
INSERT INTO tags (name) VALUES ('cover');

CREATE TABLE image_tag (
  id INT AUTO_INCREMENT PRIMARY KEY,
  image_id INT NOT NULL,
  tag_id INT NOT NULL
);
INSERT INTO image_tag (image_id, tag_id) VALUES (3, 1);
INSERT INTO image_tag (image_id, tag_id) VALUES (4, 1);

CREATE TABLE album_image (
  id INT AUTO_INCREMENT PRIMARY KEY,
  album_id INT NOT NULL,
  image_id INT NOT NULL
);
INSERT INTO album_image (album_id, image_id) VALUES (1, 1);
INSERT INTO album_image (album_id, image_id) VALUES (2, 2);
INSERT INTO album_image (album_id, image_id) VALUES (2, 3);
INSERT INTO album_image (album_id, image_id) VALUES (2, 4);
INSERT INTO album_image (album_id, image_id) VALUES (3, 5);
INSERT INTO album_image (album_id, image_id) VALUES (3, 6);

CREATE TABLE albums (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name TEXT NOT NULL,
  parent_id TEXT
);
INSERT INTO albums (name, parent_id) VALUES ('album 1', NULL);
INSERT INTO albums (name, parent_id) VALUES ('album 2', '1,');
INSERT INTO albums (name, parent_id) VALUES ('album 3', '1,');

A:

Here is my solution. 
SELECT ai.image_id AS image_id, (
    SELECT COUNT(*) FROM albums AS a2
        INNER JOIN album_image AS ai2 ON ai2.album_id=a2.id 
        LEFT JOIN image_tag AS it2 
            INNER JOIN tags AS t2 ON t2.id = it2.tag_id 
        ON it2.image_id = ai2.image_id AND t2.name = 'cover'
        WHERE a2.id = a.id AND t2.name IS NOT NULL AND it.tag_id IS NULL
    ) AS count FROM albums AS a 
INNER JOIN album_image AS ai ON ai.album_id=a.id 
LEFT JOIN image_tag AS it 
    INNER JOIN tags AS t ON t.id = it.tag_id 
ON it.image_id = ai.image_id AND t.name = 'cover'
WHERE a.id = 1 OR parent_id LIKE '1,%' 
ORDER BY CASE 
    WHEN it.tag_id IS NOT NULL AND a.id = 1 THEN RAND() 
    WHEN count > 0 THEN RAND() + 2
    ELSE RAND() + 1 
END ASC LIMIT 1;

