title: MYSQL - Creating a View With Multiple Tables
body: <p>I am having trouble creating a view with multiple tables and junction tables.</p>
  
  <p>This is where I'm at currently:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Table schema:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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:</p>
  
  <p>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. </p>