Get subitem of subitems-CodePudding

I have three tables: Playlist , PlaylistItem , Track within a SQLite Database.

Each Playlist contains multiple PlaylistItem , which contain one Track.

My goal is to provide an array of Playlist IDs and get all the Tracks inside those playlist (not only their IDs). However, I do not know how to access subitems from a query. I used joins a bit before, 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 joint, but my real Track table has over 100 keys and they could change in the future.

The tables:

Playlist
 - id INTEGER

PlaylistItem
 - id         INTEGER
 - trackID    INTEGER
 - playlistID Integer

Track
 - id    Integer
 - title String

My desired outcome would be a table which contains only the tracks with all their keys of the playlists (excluding all keys from playlist) like this :

 -- Desired outcome for the fiddle   ----
 -- id  -- title                     ----
 -- 2      Foo                       ----
 -- 22     Bar                       ----
 -- 222    Byy                       ----
 -- -------------------------------------

I achieved to get the track IDs right 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'

CodePudding user response：

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.

CodePudding user response：

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);