Debugging a rather difficult/complex MySQL query

Question

I'm having troubles in making a rather difficult MySQL query work. I've been trying, but creating complex queries has never been my strong side.

This query includes 4 tables, which I'll describe of course.

First, we have song table, which I need to select the needed info from.

+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| ID           | int(6)    | NO   | PRI | -       | auto_increment |
| Anime        | char(100) | NO   |     | -       |                |
| Title        | char(100) | NO   |     | -       |                |
| Type         | char(20)  | NO   |     | -       |                |
| Singer       | char(50)  | NO   |     | -       |                |
| Youtube      | char(30)  | NO   |     | -       |                |
| Score        | double    | NO   |     | 0       |                |
| Ratings      | int(8)    | NO   |     | 0       |                |
| Favourites   | int(7)    | NO   |     | 0       |                |
| comments     | int(11)   | NO   |     | 0       |                |
| release_year | int(4)    | NO   |     | 2019    |                |
| season       | char(10)  | NO   |     | Spring  |                |
+--------------+-----------+------+-----+---------+----------------+

Then we have song_ratings, which basically represents the lists of each user, since once you rate a song, it appears on your list.

+------------+----------+------+-----+-------------------+----------------+
| Field      | Type     | Null | Key | Default           | Extra          |
+------------+----------+------+-----+-------------------+----------------+
| ID         | int(11)  | NO   | PRI | 0                 | auto_increment |
| UserID     | int(11)  | NO   | MUL | 0                 |                |
| SongID     | int(11)  | NO   | MUL | 0                 |                |
| Rating     | double   | NO   |     | 0                 |                |
| RatedAt    | datetime | NO   |     | CURRENT_TIMESTAMP |                |
| Favourited | int(1)   | NO   |     | 0                 |                |
+------------+----------+------+-----+-------------------+----------------+

Users have the option to create custom lists(playlists), and this is the table which they are stored in. This is table lists.

+------------+-----------+------+-----+-------------------+----------------+
| Field      | Type      | Null | Key | Default           | Extra          |
+------------+-----------+------+-----+-------------------+----------------+
| ID         | int(11)   | NO   | PRI | 0                 | auto_increment |
| userID     | int(11)   | NO   | MUL | 0                 |                |
| name       | char(50)  | NO   |     | -                 |                |
| likes      | int(11)   | NO   |     | 0                 |                |
| favourites | int(11)   | NO   |     | 0                 |                |
| created_at | datetime  | NO   |     | CURRENT_TIMESTAMP |                |
| cover      | char(100) | NO   |     | -                 |                |
| locked     | int(1)    | NO   |     | 0                 |                |
| private    | int(1)    | NO   |     | 0                 |                |
+------------+-----------+------+-----+-------------------+----------------+

And finally, the table which contains all the songs that have been added to any playlists, called list_elements.

+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| ID     | int(11) | NO   | PRI | 0       | auto_increment |
| listID | int(11) | NO   | MUL | 0       |                |
| songID | int(11) | NO   | MUL | 0       |                |
+--------+---------+------+-----+---------+----------------+

What my query needs to do is list all the songs that are on the list of a user, basically these are the record in song_ratings where the userID = ?(obviously the ID of the user), but are not on a specific playlist(has no record in list_elements) where the ID/listID = ?(the ID of that playlist).

This is the query I've been using so far, but after a while I had realized this doesn't actually work the way I wanted to.

SELECT DISTINCT
    COUNT(*)
FROM
    song
INNER JOIN song_ratings ON song_ratings.songID = song.ID
LEFT JOIN list_elements ON song_ratings.songID = list_elements.songID
WHERE
    song_ratings.userID = 34 AND list_elements.songID IS NULL

I have also tried something like this, and several variants of it

SELECT DISTINCT
   COUNT(*)
FROM
    song
INNER JOIN song_ratings ON song_ratings.songID = song.ID

INNER JOIN lists ON lists.userID = song_ratings.userID

LEFT JOIN list_elements ON song_ratings.songID = list_elements.songID

WHERE
    song_ratings.userID = 34 AND lists.ID = 1

To make it easier, here's a SQL Fiddle, with all the necessary tables and records in them.

What you need to know. When you check for the playlist with the ID of 1, the query needs to return 23(basically all matches).

When you do the same with the ID 4, it need to return 21, if the query works correctly, because the playlist 1 is empty, thus all of the songs in the table song_ratings can be added to it(at least the ones that exist in song table, which is only half of the overall records now).

But playlist 4 already has 2 songs added to it, so only 21 are left available for adding.

Or in case the number are wrong, playlist 1 needs to return all matches. playlist 4 need to return all matches-2(because 2 songs are already added).

The userID needs to remain the same(34), and there are no records with different ID, so don't change it.

I hope I managed to explain it properly I would suggest you failed in that endeavour, but not to worry, just see: Why should I provide an MCRE for what seems to me to be a very simple SQL query? — Strawberry
– Strawberry, Commented Jan 24, 2020 at 13:21
Incidentally, the number in parentheses in the INT() data type, is almost entirely meaningless. — Strawberry
– Strawberry, Commented Jan 24, 2020 at 13:22
@halfer I get that, and know it, but I still don't prefer that style, because that way it just seems like I'm demanding an asnwer, which is technically true, but it'd still be a rather impolite way to do it in my opinion. — K. P.
– K. P., Commented Feb 1, 2020 at 11:56
The community has decided upon this already. See also the sidebar for related questions. We are firmly decided, at least for now, that succinct technical writing is preferred. — halfer
– halfer, Commented Feb 1, 2020 at 11:58
I came here trying to find a better way to debug 300+ line queries with multiple joins, unions and filtering. The title + finding the query in question was less than 10 lines made me chuckle :D (happy to see you found a good solution too) :D BTW if any of you reading that comment has some resource on that. Please DO leave me a link. Highly appreciated. — Hop hop
– Hop hop, Commented Jan 19, 2023 at 6:31

rai · Accepted Answer · 2020-01-24 13:25:35Z

2

You could try subquery with NOT IN clause

SELECT DISTINCT
COUNT(*)
FROM
song
INNER JOIN song_ratings ON song_ratings.songID = song.ID
WHERE
song_ratings.userID = 34 AND song.ID not in (select songID from list_elements group by songID)

answered Jan 24, 2020 at 13:25

rai

5194 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

K. P. Over a year ago

With checking the listID, by adding WHERE listID=? it works perfectly! Thank you so much!

slaakso · Accepted Answer · 2023-01-19 09:16:22Z

2

Your original query was almost correct. When you use a column from a joined table with a LEFT JOIN in the WHERE-clause, it causes the LEFT JOIN to turn into an INNER JOIN.

You can put the condition into the ON-clause:

SELECT COUNT(*)
FROM song
  INNER JOIN song_ratings ON song_ratings.songID = song.ID
  LEFT JOIN list_elements ON song_ratings.songID = list_elements.songID 
                             AND list_elements.songID IS NULL
WHERE song_ratings.userID = 34

Using JOINs in MySQL is faster than using subqueries, this would probably be faster as well.

Btw, you do not need DISTINCT when you only have COUNT(*). The COUNT(*) returns only one row so there is no need to take distinct values from one value.

edited Jan 19, 2023 at 9:16

answered Jan 24, 2020 at 16:21

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Collectives™ on Stack Overflow

Debugging a rather difficult/complex MySQL query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related