CODEWITHSUNDEEP
joinsqliteintersect
tetris11
tetris11

Reputation: 817

Sqlite3: Selecting from multiple tables without duplicates

I've got three tables:

paper:             items:               attachments:
============       ==============       ==============
jkey | title       itemID | jkey*       itemID* | path

*foreign key from another table

I'm trying to retrieve the title of all papers and their associated attachment paths, for all papers that have attachments.

Current attempt is:

SELECT paper.title,attachments.path IN paper,attachments
WHERE paper.jkey IN (
    SELECT items.jkey FROM items,attachments
    WHERE items.itemID = attachments.itemID
);

Unfortunately this just seems to print gibberish (the same path for different titles and vice versa).

What am I doing wrong?

Upvotes: 0

Views: 99

Answers (1)

CL.
CL.

Reputation: 180080

If you want to join, you should use joins:

SELECT paper.title,
       attachments.path
FROM paper
JOIN items USING (jkey)
JOIN attachments USING (itemID);

To omit duplicate rows, use SELECT DISTINCT ... instead.

Upvotes: 1

Related Questions