find many to many with no relation

Question

I have an items table, a tags table and a reletations table. I need to find untagged items.

With this (working) query I find the tags number for each item:

SELECT i.id, i.text, COUNT( ti.item ) AS count
FROM items AS i
JOIN topic_item AS ti ON ti.item = i.id
GROUP BY ti.item

If tried to modify the query with:

ti.item != i.id

or I add

WHERE count = 0

But it's not the right way. Which is the right approach?

Answer 1

You can use a left join, and get the items where there is no matching tag:

SELECT i.id, i.text
FROM items AS i
LEFT JOIN topic_item AS ti ON ti.item = i.id
WHERE ti.item is null

You can also use not exists:

SELECT i.id, i.text
FROM items AS i
WHERE not exists(
  select * from topic_item AS ti
  where ti.item = i.id
)