1

I have a basic problem about create a query for listing artists with a condition of the artist have songs with all format type, the database have three table ( check ER diagram ).

The Question is: Find all the artist names with songs in all the possible mediatypes

enter image description here

and the MediaType is listed here:

  1. MPEG audio file
  2. Protected AAC audio file
  3. Protected MPEG-4 video file . .

I tried this, but I stuck in the where clause

SELECT a.[Name]
FROM track t INNER JOIN Album alb ON t.AlbumId = alb.AlbumId
 INNER JOIN Artist a ON alb.ArtistId = a.ArtistID
 INNER JOIN MediaType m ON t.MediaTypeId = m.MediaTypeId
WHERE 

This is an overview of the SQL DDL statements:

CREATE TABLE MediaType ( MediaTypeId int(1) PRIMARY KEY, [Name] varchar(30) );
CREATE TABLE Artist(ArtistId int(3), [Name] varchar(100) ); -- LONG CHAR????
CREATE TABLE Album(AlbumId int(3) PRIMARY KEY, Title varchar(100), ArtistId int(3));
CREATE TABLE PlaylistTrack(TrackId int(4) PRIMARY KEY, [Name] varchar(220), AlbumId int(3), 
 MediaTypeId int(1), GenreId int(2), Composer varchar(220), Miliseconds int(12), Bytes int(12), UnitPrice decimal(8,2)); -- UnitPrice number

This is an overview of the data resumed: Artist Table:

INSERT INTO `Artist` (`ArtistId`, `Name`) VALUES (1, N'AC/DC');
INSERT INTO `Artist` (`ArtistId`, `Name`) VALUES (2, N'Accept');
INSERT INTO `Artist` (`ArtistId`, `Name`) VALUES (3, N'Aerosmith');
INSERT INTO `Artist` (`ArtistId`, `Name`) VALUES (4, N'Alanis Morissette');
INSERT INTO `Artist` (`ArtistId`, `Name`) VALUES (5, N'Alice In Chains');
INSERT INTO `Artist` (`ArtistId`, `Name`) VALUES (6, N'Antônio Carlos Jobim');
INSERT INTO `Artist` (`ArtistId`, `Name`) VALUES (7, N'Apocalyptica');
INSERT INTO `Artist` (`ArtistId`, `Name`) VALUES (8, N'Audioslave');
INSERT INTO `Artist` (`ArtistId`, `Name`) VALUES (9, N'BackBeat');
INSERT INTO `Artist` (`ArtistId`, `Name`) VALUES (10, N'Billy Cobham');
.
.

Album Table:

INSERT INTO `Album` (`AlbumId`, `Title`, `ArtistId`) VALUES (1, N'For Those About To Rock We Salute You', 1);
INSERT INTO `Album` (`AlbumId`, `Title`, `ArtistId`) VALUES (2, N'Balls to the Wall', 2);
INSERT INTO `Album` (`AlbumId`, `Title`, `ArtistId`) VALUES (3, N'Restless and Wild', 2);
INSERT INTO `Album` (`AlbumId`, `Title`, `ArtistId`) VALUES (4, N'Let There Be Rock', 1);
.
.

Track Table:

INSERT INTO `Track` (`TrackId`, `Name`, `AlbumId`, `MediaTypeId`, `GenreId`, `Composer`, `Milliseconds`, `Bytes`, `UnitPrice`) VALUES (1, N'For Those About To Rock (We Salute You)', 1, 1, 1, N'Angus Young, Malcolm Young, Brian Johnson', 343719, 11170334, 0.99);
INSERT INTO `Track` (`TrackId`, `Name`, `AlbumId`, `MediaTypeId`, `GenreId`, `Milliseconds`, `Bytes`, `UnitPrice`) VALUES (2, N'Balls to the Wall', 2, 2, 1, 342562, 5510424, 0.99);
INSERT INTO `Track` (`TrackId`, `Name`, `AlbumId`, `MediaTypeId`, `GenreId`, `Composer`, `Milliseconds`, `Bytes`, `UnitPrice`) VALUES (3, N'Fast As a Shark', 3, 2, 1, N'F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman', 230619, 3990994, 0.99);
INSERT INTO `Track` (`TrackId`, `Name`, `AlbumId`, `MediaTypeId`, `GenreId`, `Composer`, `Milliseconds`, `Bytes`, `UnitPrice`) VALUES (4, N'Restless and Wild', 3, 2, 1, N'F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman', 252051, 4331779, 0.99);
INSERT INTO `Track` (`TrackId`, `Name`, `AlbumId`, `MediaTypeId`, `GenreId`, `Composer`, `Milliseconds`, `Bytes`, `UnitPrice`) VALUES (5, N'Princess of the Dawn', 3, 2, 1, N'Deaffy & R.A. Smith-Diesel', 375418, 6290521, 0.99);
INSERT INTO `Track` (`TrackId`, `Name`, `AlbumId`, `MediaTypeId`, `GenreId`, `Composer`, `Milliseconds`, `Bytes`, `UnitPrice`) VALUES (6, N'Put The Finger On You', 1, 1, 1, N'Angus Young, Malcolm Young, Brian Johnson', 205662, 6713451, 0.99);
INSERT INTO `Track` (`TrackId`, `Name`, `AlbumId`, `MediaTypeId`, `GenreId`, `Composer`, `Milliseconds`, `Bytes`, `UnitPrice`) VALUES (7, N'Let''s Get It Up', 1, 1, 1, N'Angus Young, Malcolm Young, Brian Johnson', 233926, 7636561, 0.99);
.
.

MediaType Table:

INSERT INTO `MediaType` (`MediaTypeId`, `Name`) VALUES (1, N'MPEG audio file');
INSERT INTO `MediaType` (`MediaTypeId`, `Name`) VALUES (2, N'Protected AAC audio file');
INSERT INTO `MediaType` (`MediaTypeId`, `Name`) VALUES (3, N'Protected MPEG-4 video file');
INSERT INTO `MediaType` (`MediaTypeId`, `Name`) VALUES (4, N'Purchased AAC audio file');
INSERT INTO `MediaType` (`MediaTypeId`, `Name`) VALUES (5, N'AAC audio file');

My mysql not work at the moment for display an ER Diagram but I can draw with paint

I understand that my problem is related to join clause but I can figure out how to execute this query.

5
  • 1
    Unclear. In some code you use square brackets which are not distinguished by MySQL, but in another code you use backticks... please check your DBMS carefully.
    – Akina
    Sep 6 at 5:05
  • For to solve you must group by an artist and count the amount of distinct mediatypes for the artist.
    – Akina
    Sep 6 at 5:06
  • I am having problems with MySQL because is blocked in my country. I am trying to do something and when I fix this problem, execute the queries. I need to do this in MySQL in my last work I had to use Teamviewer to do something. Sep 6 at 5:07
  • 2
    I am having problems with MySQL because is blocked in my country. This is not a problem, use any online fiddle. For example, dbfiddle.uk/… (all typos are fixed).
    – Akina
    Sep 6 at 5:20
  • thk for the hint @Akina and for your information for execute MySQL code online but my database was too big for run in browser. I had to use teamviewer again. thk u for your hints help me a lot. Sep 16 at 3:26
0

All the credits to @Akina for hint me.

This query work:

SELECT a.Name
FROM Artist a 
INNER JOIN Album alb ON a.ArtistId = alb.ArtistId
INNER JOIN Track t ON alb.AlbumId = t.AlbumID
INNER JOIN MediaType m ON t.MediaTypeId = m.MediaTypeId
GROUP BY a.ArtistId, a.Name
HAVING COUNT(DISTINCT m.MediaTypeId) = 5

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.