SQLite - Connecting 3 Database tables

Question

I have this 3 tables in my database:

Users: with the keys: ID (primary key) | name

Albums: with the keys: ID (primary key) | name | USER_ID

Pictures: with the keys: ID (primary key) | name | ALBUM_ID

In the Albums table, the USER_ID is the ID of the user that owns the album, and in the Pictures table, the ALBUM_ID is the ID of the album that the picture is belong to.

I'm trying to print every picture next to the user who owns it, I want my result to be like that:

Pictures.name    |    Users.name

So, I tried to connect the Pictures to the Albums using the Album_ID and then connect the Albums table to the Users, but it didn't work.

Answer 1

You don't connect tables you connect the database which then gives you access to the tables in the database.

So once you have connected to the database, to get your list you would use/run a query along the lines of :-

SELECT Pictures.name, Users.name 
FROM Pictures 
    JOIN Albums ON Pictures.ALBUM_ID = Albums.ID
    JOIN Users ON Albums.ALBUM_ID = Users.USER_ID
;

Example

The Tables

As an example the connected database has the following tables (note table names and column names changed for convenience) :-

The Result

Using a query similar to the above (just different table and column names) :-

SELECT _pictures.name, _users.name 
FROM _pictures 
    JOIN _albums ON _pictures.albumid = _albums.id
    JOIN _users ON _albums.userid = _users.id
;

results in :-

---

The example was based upon the following SQL to create and populate the tables :-

CREATE TABLE IF NOT EXISTS _users (ID INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE IF NOT EXISTS _albums (ID INTEGER PRIMARY KEY, name TEXT, userid INTEGER);
CREATE TABLE IF NOT EXISTS _pictures (ID INTEGER PRIMARY KEY, name TEXT, albumid INTEGER);
INSERT INTO _users VALUES 
    (1,'Fred'),
    (2,'Bert'),
    (3,'Harry')
;
INSERT INTO _albums VALUES(1,'Ablum owned by Fred',1),
    (2,'Another Album owned by Fred',1),
    (3,'Another Album owned by Bert',2),
    (4,'Another Album owned by Harry',3),
    (5,'An Album for Fred',1),
    (6,'The Album of Harry',3),
    (7,'Harry the Album',3),
    (8,'A taste of Bert',2)
;
INSERT INTO _pictures VALUES
    (1,'Picture for Album 1',1),
    (2,'Picture for Album 2',2),
    (3,'Picture for Album 3',3),
    (4,'Picture for Album 1',4),
    (5,'Picture for Album 1',5),
    (6,'Picture for Album 1',6),
    (7,'Picture for Album 1',7),
    (8,'Picture for Album 1',8)
;