CODEWITHSUNDEEP
mysqljoin
rubengavidia0x
rubengavidia0x

Reputation: 590

Joining Tables For Find the Playlists without any track of the genres “Latin”, “Rock” or “Pop”

I have a basic problem about create a query for Find the Playlists without any track of the genres “Latin”, “Rock” or “Pop”. I am really struggling with Joins some exercises I can do well but with this I can figure out.

I tried This:

SELECT p.PlaylistId as Playlist, p.Name
FROM Playlist p INNER JOIN PlaylistTrack pt ON p.PlaylistId = pt.PlaylistId 
INNER JOIN Track t ON pt.TrackId = t.TrackId
JOIN Genre g ON g.GenreId = t.GenreId
WHERE g.Name <> "Latin " AND "Rock" AND "Pop"

My Output are returning 1 value: AC/DC, and null values when I use LEFT JOIN.

This is my DDL:

CREATE TABLE Genre (GenreId int PRIMARY KEY, `Name` CHAR(255) );
CREATE TABLE MediaType ( MediaTypeId int PRIMARY KEY, `Name` CHAR(30) );
CREATE TABLE Artist(ArtistId INT PRIMARY KEY, `Name` CHAR(255) ); -- LONG CHAR????
CREATE TABLE Album(AlbumId INT PRIMARY KEY, Title CHAR(120), ArtistId INT );
CREATE TABLE Track( TrackId int PRIMARY KEY, `Name` CHAR(255), AlbumId INT, 
 MediaTypeId INT, GenreId INT, Composer CHAR(220), Milliseconds int, Bytes INT, UnitPrice decimal(8,2)); -- UnitPrice number
CREATE TABLE Playlist(PlaylistId int PRIMARY KEY, `Name` CHAR(30));

If you are more interested and want to test this database that I build check this link, is a little dirty and need to be improve but commenting some rows will work.

As I Said: My Output are returning 1 value: AC/DC, and null values when I use LEFT JOIN.

This is the expected value:

PlaylistId Name
2, Movies
5 TV Shows
6 Audiobooks
7 Audiobooks
8 Movies
9 Music Videos
10 TV Shows
11 Classical
12 Classical 101 - Deep Cuts
13 Classical 101 - Next Step
14 Classical 101 - The Basics
15 On-The-Go 1

I thought that this question was really easy. I dont understand why I can't figure out, can someone help me?

Upvotes: 0

Views: 267

Answers (1)

Akina
Akina

Reputation: 42728

SELECT *
FROM Playlist 
WHERE NOT EXISTS ( SELECT NULL
                   FROM PlaylistTrack
                   INNER JOIN Track USING (TrackId)
                   INNER JOIN Genre USING (GenreId)
                   WHERE Playlist.PlaylistId = PlaylistTrack.PlaylistId
                     AND Genre.Name IN ('Latin', 'Rock', 'Pop') )

Upvotes: 2

Related Questions