Get subitem of subitems

Question

I have three tables:

CREATE TABLE Playlist
    (`id` int, `name` varchar(90))
;
    
INSERT INTO Playlist
    (`id`, `name`)
VALUES
    (1, 'Playlist_1'),
    (100, 'EmptyPlaylist'),
    (111, 'Playlist_222'),
    (1001, 'Playlist_4')
;

CREATE TABLE PlaylistItem
    (`id` int, `trackID` int, `playlistID` int)
;
    
INSERT INTO PlaylistItem
    (`id`, `trackID`, `playlistID`)
VALUES
    (3, 2, 1),
    (32, 22, 1),
    (321, 222, 1),
    (333, 222, 3),
    (333, 2, 3),
    (303, 200, 1001)
;

CREATE TABLE Track
    (`id` int, `title` varchar(300))
;
    
INSERT INTO Track
    (`id`, `title`)
VALUES
    (2, 'Foo'),
    (22, 'Bar'),
    (200, 'Only_In_Playlist_4_Which_Is_Not_Included_In_the_Query'),
    (222, 'Byy'),
    (21, 'NotInAnyPlaylist'),
    (20000, 'NotInAnyPlaylist_2')
;

Fiddle

Each Playlist contains multiple PlaylistItem , which contain one Track. My goal is to provide an array of Playlist IDs and get all Tracks inside those playlist (not only their IDs).

However, I do not know how to access subitems from a query. I used joins but they merge the keys and I only want to get the tracks without the keys from other tables. I could select just the keys from Track with a join, but my Track table has over 100 keys and they could change.

Desired outcome :

id	title
2	Foo
22	Bar
222	Byy

I get track IDs now with this:

SELECT 
  trackID
FROM
  PlaylistItem
WHERE
  playlistID in ('1', '4') -- Here comes the array of IDs, for the example I want '1' and '4'

[ask] [Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/q/271055) — PChemGuy, Nov 05 '22 at 12:45
@PChemGuy I added a fiddle and what I tried. Is this good now? — Woww, Nov 05 '22 at 12:56
That's better. You also need to show a table with desired result — PChemGuy, Nov 05 '22 at 12:58
I do not see how your target is different from the content of the Track table — PChemGuy, Nov 05 '22 at 13:03
And you need to clearly state your problem. Right now you do not even mention it. — PChemGuy, Nov 05 '22 at 13:07
Will likely be easier next time ;) Thanks for editing and improving your question! — Jonas Metzler, Nov 05 '22 at 14:00

Jonas Metzler · Accepted Answer · 2022-11-05T13:57:18.837

Assuming that your description is correct now and you didn't miss to tell us anything important, you just need to JOIN your tables and select those columns that should be shown.

This query will produce exactly the outcome you described:

SELECT p.id, t.title
FROM playlist p
JOIN PlaylistItem tp
ON p.id = tp.playlistID
JOIN track t 
ON t.id = tp.trackID;

You can verify this here: db<>fiddle

If you need a condition which playlists should be shown, you can add a WHERE clause or add the condition to the first JOIN. Doing this in the WHERE clause is mostly better readable, doing it in the JOIN can reduce the query's execution time.

For example, if you want to show playlists 1 and 4 only:

SELECT p.id, t.title
FROM playlist p
JOIN PlaylistItem tp
ON p.id = tp.playlistID
JOIN track t 
ON t.id = tp.trackID
WHERE p.id IN (1,4);

OR:

SELECT p.id, t.title
FROM playlist p
JOIN PlaylistItem tp
ON p.id = tp.playlistID AND p.id IN (1,4)
JOIN track t 
ON t.id = tp.trackID;

If not only the track's id, but also further columns from this table should be shown, add those columns to the selection:

SELECT p.id, t.title, t.column2, t.column3,...
FROM playlist p
JOIN PlaylistItem tp
ON p.id = tp.playlistID
JOIN track t 
ON t.id = tp.trackID
WHERE p.id IN (1,4);

You could also just write ...,t.*, but using * is not recommended.

A last note: Like forpas showed, in your specific case, you even could use just one JOIN. My answer assumed that's because you just showed a small part of your real data and you need all tables in a real scenario. But you should of course check this and if really not required, remove the second JOIN.

This works for the example, but I my real 'Track' table has lots of keys (I added this information later, so you might have not seen it). And I might also want to change the keys in the future — Woww, Nov 05 '22 at 13:37
But changing the SELECT to t.* works (in the second and third example)! Can you edit your answer to that so that I can accept it? — Woww, Nov 05 '22 at 13:38
@Woww I edited the answer...using * is mostly no good idea, you should better set a proper column selection. — Jonas Metzler, Nov 05 '22 at 13:42

score 1 · Answer 2 · answered Nov 05 '22 at 13:43

1

You only need 1 join:

SELECT p.id, t.*
FROM track t INNER JOIN PlaylistItem p
ON p.trackID = t.id
WHERE p.playlistID IN (1, 4);

answered Nov 05 '22 at 13:43

forpas

160,666
10
38
76

Get subitem of subitems

2 Answers2