MYSQL - Creating a View With Multiple Tables
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.
mysql
asked Nov 14 '18 at 2:05
Tony Tony
103
add a comment |
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.
mysql
asked Nov 14 '18 at 2:05
Tony Tony
103
What is the question?
– Daniel Tran
Nov 14 '18 at 2:07
1
You are do join but where the condition you join?
– dwir182
Nov 14 '18 at 2:10
Would I need to do a subquery to populate my results? There is a junction table called rec_artist containing artist_id and rec_id. This links the recordings table and artist table. There is also a genres table linking to the recordings table.
– Tony
Nov 14 '18 at 2:13
Please, inlcude the schema of the tables that are involved on your query. Also, take a read to Joins Tutorial
– Shidersz
Nov 14 '18 at 2:51
add a comment |
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.
mysql
asked Nov 14 '18 at 2:05
Tony Tony
103
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.
mysql
mysql
asked Nov 14 '18 at 2:05
Tony Tony
103
asked Nov 14 '18 at 2:05
Tony Tony
103
edited Nov 14 '18 at 3:05
Tony
asked Nov 14 '18 at 2:05
Tony Tony
103
asked Nov 14 '18 at 2:05
Tony Tony
103
asked Nov 14 '18 at 2:05
Tony Tony
103
103
What is the question?
– Daniel Tran
Nov 14 '18 at 2:07
1
You are do join but where the condition you join?
– dwir182
Nov 14 '18 at 2:10
Would I need to do a subquery to populate my results? There is a junction table called rec_artist containing artist_id and rec_id. This links the recordings table and artist table. There is also a genres table linking to the recordings table.
– Tony
Nov 14 '18 at 2:13
Please, inlcude the schema of the tables that are involved on your query. Also, take a read to Joins Tutorial
– Shidersz
Nov 14 '18 at 2:51
add a comment |
What is the question?
– Daniel Tran
Nov 14 '18 at 2:07
1
You are do join but where the condition you join?
– dwir182
Nov 14 '18 at 2:10
Would I need to do a subquery to populate my results? There is a junction table called rec_artist containing artist_id and rec_id. This links the recordings table and artist table. There is also a genres table linking to the recordings table.
– Tony
Nov 14 '18 at 2:13
Please, inlcude the schema of the tables that are involved on your query. Also, take a read to Joins Tutorial
– Shidersz
Nov 14 '18 at 2:51
What is the question?
– Daniel Tran
Nov 14 '18 at 2:07
What is the question?
– Daniel Tran
Nov 14 '18 at 2:07
1
1
You are do join but where the condition you join?
– dwir182
Nov 14 '18 at 2:10
You are do join but where the condition you join?
– dwir182
Nov 14 '18 at 2:10
Would I need to do a subquery to populate my results? There is a junction table called rec_artist containing artist_id and rec_id. This links the recordings table and artist table. There is also a genres table linking to the recordings table.
– Tony
Nov 14 '18 at 2:13
Would I need to do a subquery to populate my results? There is a junction table called rec_artist containing artist_id and rec_id. This links the recordings table and artist table. There is also a genres table linking to the recordings table.
– Tony
Nov 14 '18 at 2:13
Please, inlcude the schema of the tables that are involved on your query. Also, take a read to Joins Tutorial
– Shidersz
Nov 14 '18 at 2:51
Please, inlcude the schema of the tables that are involved on your query. Also, take a read to Joins Tutorial
– Shidersz
Nov 14 '18 at 2:51
add a comment |
1 Answer
1
active
oldest
votes
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;
answered Nov 14 '18 at 4:08
ShiderszShidersz
6,1542730
Thank you!! That makes so much sense. I appreciate the help.
– Tony
Nov 14 '18 at 4:22
@Tony you are welcome, take care of marks the answer as correct if this worked for you.
– Shidersz
Nov 14 '18 at 16:51
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53292168%2fmysql-creating-a-view-with-multiple-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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;
answered Nov 14 '18 at 4:08
ShiderszShidersz
6,1542730
Thank you!! That makes so much sense. I appreciate the help.
– Tony
Nov 14 '18 at 4:22
@Tony you are welcome, take care of marks the answer as correct if this worked for you.
– Shidersz
Nov 14 '18 at 16:51
add a comment |
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;
answered Nov 14 '18 at 4:08
ShiderszShidersz
6,1542730
Thank you!! That makes so much sense. I appreciate the help.
– Tony
Nov 14 '18 at 4:22
@Tony you are welcome, take care of marks the answer as correct if this worked for you.
– Shidersz
Nov 14 '18 at 16:51
add a comment |
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;
answered Nov 14 '18 at 4:08
ShiderszShidersz
6,1542730
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;
answered Nov 14 '18 at 4:08
ShiderszShidersz
6,1542730
answered Nov 14 '18 at 4:08
ShiderszShidersz
6,1542730
answered Nov 14 '18 at 4:08
ShiderszShidersz
6,1542730
answered Nov 14 '18 at 4:08
ShiderszShidersz
6,1542730
6,1542730
Thank you!! That makes so much sense. I appreciate the help.
– Tony
Nov 14 '18 at 4:22
@Tony you are welcome, take care of marks the answer as correct if this worked for you.
– Shidersz
Nov 14 '18 at 16:51
add a comment |
Thank you!! That makes so much sense. I appreciate the help.
– Tony
Nov 14 '18 at 4:22
@Tony you are welcome, take care of marks the answer as correct if this worked for you.
– Shidersz
Nov 14 '18 at 16:51
Thank you!! That makes so much sense. I appreciate the help.
– Tony
Nov 14 '18 at 4:22
Thank you!! That makes so much sense. I appreciate the help.
– Tony
Nov 14 '18 at 4:22
@Tony you are welcome, take care of marks the answer as correct if this worked for you.
– Shidersz
Nov 14 '18 at 16:51
@Tony you are welcome, take care of marks the answer as correct if this worked for you.
– Shidersz
Nov 14 '18 at 16:51
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53292168%2fmysql-creating-a-view-with-multiple-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What is the question?
– Daniel Tran
Nov 14 '18 at 2:07
1
You are do join but where the condition you join?
– dwir182
Nov 14 '18 at 2:10
Would I need to do a subquery to populate my results? There is a junction table called rec_artist containing artist_id and rec_id. This links the recordings table and artist table. There is also a genres table linking to the recordings table.
– Tony
Nov 14 '18 at 2:13
Please, inlcude the schema of the tables that are involved on your query. Also, take a read to Joins Tutorial
– Shidersz
Nov 14 '18 at 2:51