Selecting all rows with a column that is contained in query result

Question

I have two tables:

CREATE TABLE Master (
    url TEXT PRIMARY KEY,
    masterId TEXT,
);

CREATE TABLE Version (
    verId TEXT PRIMARY KEY,
    isPending INTEGER,
    masterId TEXT,
    FOREIGN KEY(masterId) REFERENCES Master(masterId)
);

I want to select all pending urls. Meaning all urls from Master that has a row in Version with the same masterId and isPending = 1.

How to write such nested select?

You can't create those two tables like that. (Invalid foreign key...) — jarlh, Nov 14 '18 at 15:20
A foreign key must reference a key. (Either the primary key or a unique constraint.) — jarlh, Nov 14 '18 at 15:50
@GordonLinoff Can't I just use something like: `@"SELECT url FROM Master WHERE masterId IN (SELECT masterId, FROM Version WHERE isPending = 1)` — Sanich, Nov 14 '18 at 15:51

score 0 · Answer 1 · edited Nov 14 '18 at 15:51

0

Use join

SELECT m.*
     , v.*
FROM   Master m
JOIN   version v
  ON m.masterId = v.masterId
WHERE  isPending = 1

edited Nov 14 '18 at 15:51

Kobi

2,494
15
30

answered Nov 14 '18 at 15:18

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 2 · answered Nov 14 '18 at 15:22

0

If you just need the URL's that are pending then you could use an EXISTS

SELECT url
FROM Master m
WHERE EXISTS
(
  SELECT 1
  FROM Version v
  WHERE v.masterId = m.masterId
    AND v.isPending = 1
);

answered Nov 14 '18 at 15:22

LukStorms

28,916
5
31
45

Selecting all rows with a column that is contained in query result

2 Answers2