Search code examples
joinsqliteintersect

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?

Solution

  • 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.