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