Retrieving data from multiple tables with a MySQL query

Hi there

I need to be able to retrieve all of an Artist's songs from a database given an artist's name.

Here is our ERD;

I don't know how to select (or use) more than two tables at a time, as you can see I need the TrackName and the Artist info. To get a TrackName of an Artist, I need to make sure that either the ISBN matches in both the Album and Song_Selected tables or that the TrackNumber matches from the Song_Selected and Song tables.

I hope this makes sense.

Here is a query to show roughly what I am trying to do(obviously it doesn't work or I wouldn't be asking for help lol)

Select Song.TrackName, Album.Artist FROM Song INNER JOIN Album on Album.ISBN = Song_ Selected.ISBN where Album.Artist = 'Chris Brown';

Any ideas on how to get this to work?

Many thanks :smile:

Scroll to see replies

Reply 1

ct2k7

What do you mean that it doesn't work, is MySQL throwing up any errors are are you just not getting any data?

Do a left join instead as well.

Posted from TSR Mobile

Reply 2

jackie11

Original post by ct2k7

What do you mean that it doesn't work, is MySQL throwing up any errors are are you just not getting any data?

Do a left join instead as well.

Posted from TSR Mobile

This is the error I get;

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Song_Selected.ISBN" could not be bound.

How do you do a left join?

Reply 3

jackie11

Oh yeah its SQL not MySQL

Reply 4

ct2k7

Original post by jackie11

Oh yeah its SQL not MySQL

SELECT
Song.TrackName,
Album.Artist
FROM song
LEFT JOIN Song_Selected ON Song_Selected.ISBN = Album.ISBM
LEFT JOIN Song ON Song.TrackNumber = Song_Selected.TrackNumber

The above would work in MySQL, but the basic SQL / DDL syntax remains the same, assuming your RDBMS supports Left Join (left outer join).

Reply 5

jackie11

Original post by ct2k7

ok

SELECT
Song.TrackName,
Album.Artist
FROM song
LEFT JOIN Song_Selected ON Song_Selected.ISBN = Album.ISBN
LEFT JOIN Song ON Song.TrackNumber = Song_Selected.TrackNumber;

brings up this error;

Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Album.ISBN" could not be bound.
Msg 1013, Level 16, State 1, Line 1
The objects "Song" and "song" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Reply 6

jackie11

and this

SELECT
Song.TrackName,
Album.Artist
FROM song, Album
LEFT JOIN Song_Selected ON Song_Selected.ISBN = Album.ISBN
LEFT JOIN Song ON Song.TrackNumber = Song_Selected.TrackNumber;

causes error:

Msg 1013, Level 16, State 1, Line 1
The objects "Song" and "song" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Reply 7

Chrosson

Original post by jackie11

Capitalise the first letter of 'song' in your FROM clause?

I also prefer implicit joins, but that's not a big issue.

Reply 8

jackie11

Original post by Chrosson

Capitalise the first letter of 'song' in your FROM clause?

I also prefer implicit joins, but that's not a big issue.

With capitalizing the first letter of 'song' in your FROM clause, I get the same error;

Msg 1013, Level 16, State 1, Line 1
The objects "Song" and "Song" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Reply 9

jackie11

ok this seems to clear up the above issue;

SELECT
S.TrackName,
Album.Artist
FROM Song S, Album
LEFT JOIN Song_Selected ON Song_Selected.ISBN = Album.ISBN
LEFT JOIN Song ON Song.TrackNumber = Song_Selected.TrackNumber where Album.Artist = 'Chris Brown';

However this causes all the tracks in the database to be listed with the Artist name of Chris Brown, when some of the tracks have been inserted into the database with other Artists names, how do I fix this?

Reply 10

Push_More_Button

Try this:

SELECT Song.TrackName, Album.Artist
FROM (Song INNER JOIN Song_Selected ON Song.TrackNumber = Song_Selected.TrackNumber) INNER JOIN Album ON Song_Selected.ISBN = Album.ISBN
WHERE (((Album.Artist)="Chris Brown"));

Reply 11

jackie11

Original post by Push_More_Button

ok, your suggestion brings up this error;
Invalid column name 'Chris Brown'.

So I tried this;

SELECT Song.TrackName, Album.Artist
FROM (Song INNER JOIN Song_Selected ON Song.TrackNumber = Song_Selected.TrackNumber)
INNER JOIN Album ON Song_Selected.ISBN = Album.ISBN
WHERE ((Album.Artist='Chris Brown'));

But this just brings up two blank columns (TrackName, Artist)

Reply 12

Push_More_Button

It's odd that it's trying to find a column name for 'Chris Brown'.

Which version of SQL are you using?

Reply 13

Chrosson

Original post by jackie11

Ok, given we have no idea what data you actually have (and I don't use explicit JOINs), why not do some debugging and build it up yourself?

E.g.
1) select all albums where the artist is Chris Brown
2) choose an arbitrary album ISBN from 1, and select from song_selected where the ISBN is that album's ISBN.
3) now do 1 and 2 in one query (i.e. with an inner JOIN)
4) choose an arbitrary song selected tracknumber from the above list and select everything from song with that tracknumber
5) now do a join on songselected and song for that tracknumber
6) 5, but with the album ISBN you chose in 2
7) combine 2 and 6

Let us know each step, what they return, your queries and where you get stuck :smile:

Reply 14

jackie11

Original post by Push_More_Button

It's odd that it's trying to find a column name for 'Chris Brown'.

Which version of SQL are you using?

Yeah it is, I'm using SQL Server 2012 Management Studio

Reply 15

jackie11

Original post by Chrosson

1) 1)select all albums where the artist is Chris Brown
Query: SELECT * from Album where Artist = ‘Chris Brown’
Result: Returns the one and only album in the database by Chris Brown, so this works correctly

2) choose an arbitrary album ISBN from 1, and select from song_selected where the ISBN is that album's ISBN.
Query: SELECT ISBN, TrackNumber from Album, Song_Selected where Artist = 'Chris Brown' and Song_Selected.ISBN = Album.ISBN;
Result: error - Ambiguous column name 'ISBN'.
Query: SELECT ISBN from Album where Artist = 'Chris Brown'
Result: Returns the ISBN of that album 
Does the first query do what you asked, sorry I don’t quite understand what you meant in number 2)

Reply 16

Chrosson

Original post by jackie11

Ok, good start. The first query is good.

The second query is almost there - you're being told that it has two possible choices for the 'ISBN' column and doesn't know which you want. You need to do '<table>.ISBN' where '<table>' is where you want to pick up the ISBN column from.
(Because of the 'where' condition the ISBN in Album and Song_Selected happen to be the same so it doesn't matter which you pick)

Then move on to step 3 (select all entries in Song_Selected based on 'Chris Brown's albums by combining 1 and 2), then move onto 4 etc

Reply 17

jackie11

Original post by Chrosson

ok I changed it to this query:
SELECT Album.ISBN, TrackNumber from Album, Song_Selected where Artist = 'Chris Brown' and Song_Selected.ISBN = Album.ISBN;

I dont get any errors, but theres no data under the requested columns that are returned

Reply 18

jackie11

Anyone have any ideas on this?? Thanks

Reply 19

Chrosson

Original post by jackie11

In the first query you found Chris Brown's album and the ISBN. S make a query to return everything with that specific ISBN without joining (i.e. "where ISBN = '12345678'").