sql - How do I select data from a table consisting of a foreign key constraint?

Question

I have two tables, User & Playlist

Table User:

CREATE TABLE IF NOT EXISTS User (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(26) NOT Null UNIQUE,
    email TEXT NOT Null UNIQUE,
    password VARCHAR(100)
)

Table Playlist:

CREATE TABLE IF NOT EXISTS Playlist (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    image BLOB,
    private NUMERIC,
    playlistOwner VARCHAR(26),
    FOREIGN KEY(playlistOwner) REFERENCES User(id)
)

Playlist table:

What I have tried with:

SELECT * FROM Playlist
JOIN Playlist 
ON 10 = Playlist.playlistOwner
WHERE playlistOwner = ald;

Which gives the error:

question from:https://stackoverflow.com/questions/65920688/how-do-i-select-data-from-a-table-consisting-of-a-foreign-key-constraint

Answer 1

First, if you want the column playlistOwner of the table Playlist as foreign key that references the column id of the table User you should define with the same data type as the referenced column.

So the correct definition should be playlistOwner INTEGER:

CREATE TABLE IF NOT EXISTS Playlist (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    image BLOB,
    private NUMERIC,
    playlistOwner INTEGER,
    FOREIGN KEY(playlistOwner) REFERENCES User(id)
)

and the values that you store in the column playlistOwner must be the ids of the users and not their usernames.

Now if you know the user's id there is no need for a join.
You simply do:

SELECT * 
FROM Playlist
WHERE playlistOwner = 10;

If you know only the username then you join the tables:

SELECT p.* 
FROM Playlist p INNER JOIN User u
ON u.id = p.playlistOwner 
WHERE u.username = 'somename'

or with a subquery in the WHERE clause:

SELECT * 
FROM Playlist 
WHERE playlistOwner = (SELECT id FROM User WHERE username = 'somename')