Bit confused on Step 4


#1

This seems to be the answer:

CREATE TABLE tracks (id INTEGER PRIMARY KEY, title TEXT, album_ID INTEGER);

INSERT INTO tracks(id, title, album_id) VALUES (1, 'Smooth Criminal', 8);

INSERT INTO tracks(id, title, album_id) VALUES (2, 'Hey Jude', 5);

SELECT * FROM albums JOIN tracks ON albums.id = tracks.album_id ORDER BY album_id;

But it doesn't really make sense with what we were taught within the course. From the course it would make more sense to write albums.artists_id = tracks.id because id is the primary key of tracks. See the explanation in this exercise for what I'm talking about. To me it seems like album_id in tracks should be the primary key here.

Edit: Being told id in albums is the primary key would make album_id in tracks the foreign key, and would result in:

SELECT * FROM tracks JOIN albums ON tracks.album_id = albums.id ORDER BY album_id;

Maybe I'm missing something here?


#2

Also looking forward to find the answer on that task


#3

Perhaps:

SELECT * FROM albums JOIN tracks ON tracks.id = albums.id ORDER BY album_id;


#4

You have to consider what each table represents. The ID for each table represents that "thing".
So if you look at tracks.id it represents the unique ID of the tracks listed in that table.

The albums.id represents the unique ID of all the albums listed.

These are two different things (tracks versus albums). However, one of the attributes inside the TRACKS tables is called album_id. This means that the field tracks.album_id is the same thing as album.id.

Imagine if you were to draw a line from the ALBUMS table I attached above from the ID field. I would join the ALBUMS.ID field with the one in the tracks table called TRACKS.ALBUM_ID because these mean the same thing.


#5

I should point out that it is always best to uniquely name fields to avoid this kind of confusion. If I were to design this database I would have the tables structured as such:

ALBUMS
- album_id
- album_name

TRACKS
- track_id
- track_title
- album_id


#6

Hi
Try this
SELECT * FROM albums JOIN tracks ON tracks.album_id = albums.id;


#7

Select
a.ID,
a.name,
a.artist_id,
a.year,
t.id,
t.title,
t.albumn_id
from albums as a
inner join tracks as t on t.album_id=a.id
order by album_ID;


#8

Looking back on this, I was trying to use what I learned in the lessons way too hard.

How it should be done makes sense to me now.


#9

I have the same code as you and this is the outcome:

id  name            artist id year  id  title                album id
2	Elvis Presley	     2	  1956	1	Smooth Criminal	      2
3	1989		              2014	2	Heathens	          3
4	Yellow Submarine	 1	  1968	3	Love the way you lie  4
5	Hey Jude	         1    1970	4	Papaoutai             5

My issue is that the table is showing a null value under artists id which shouldn’t since inner joins only can accept the command if the condition is true, which in this case, isn’t.