Cfrgtkky

I am having trouble creating a view with multiple tables and junction tables.

This is where I'm at currently:

CREATE VIEW music_view AS 

SELECT recordings.rec_title, 

recordings.sales, 

artists.name as 'artists', 

genres.name as 'genres'

FROM 

recordings

JOIN artists

JOIN genres

JOIN rec_artist

WHERE artists.id = rec_artist.id

AND recordings.rec_id = rec_artist.rec_id

AND genres.id = recordings.genre_id;

Table schema:

recordings

rec_title (varchar)

rec_id (Primary Key)

sales (dec)

genre_id (Foreign Key)



genres

id (primary key)

name (varchar)



artists

id (primary key)

name (varchar)



rec_artist (junction table)

artist_id (primary key) 

rec_id (primary key)

I'm a little stumped as to where to proceed and am still figuring out MYSQL. Should I be doing subqueries instead of joins? My results are empty set.
The question for this particular homework assignment is as follows:

Create a view with the titles and sales of all recordings, the names of their respective artists, and the name of the recording's genre. Sort alphabetically by the name of the genre. Within the same genre, sort alphabetically by the name of the artist. Within the same artist, sort by sales (highest first).
Do not include NULL titles, genres, or artist names.
Your view must have 4 columns.

You will need a query like the next one, using inner joins for joining the tables on the adequate columns:

CREATE VIEW music_view AS 

SELECT

    r.rec_title AS 'title',

    r.sales AS 'sales',

    a.name AS 'artist', 

    g.name AS 'genre'

FROM

    recordings AS r

INNER JOIN

    rec_artists AS ra ON ra.rec_id = r.rec_id

INNER JOIN

    artists AS a ON a.id = ra.artist_id

INNER JOIN

    genres AS g ON g.id = r.genre_id

ORDER BY

    'genre' ASC, 'artist' ASC, 'sales' DESC;

And for this last step:

Do not include NULL titles, genres, or artist names

You could add some restrictions on a where clause. Like this:

CREATE VIEW music_view AS 

SELECT

    r.rec_title AS 'title',

    r.sales AS 'sales',

    a.name AS 'artist', 

    g.name AS 'genre'

FROM

    recordings AS r

INNER JOIN

    rec_artists AS ra ON ra.rec_id = r.rec_id

INNER JOIN

    artists AS a ON a.id = ra.artist_id

INNER JOIN

    genres AS g ON g.id = r.genre_id

WHERE

    r.rec_title IS NOT NULL

AND

    a.name IS NOT NULL

AND

    g.name IS NOT NULL

ORDER BY

    'genre' ASC, 'artist' ASC, 'sales' DESC;