Only join tables if a certain condition is met

Question

I'm trying to write a SQLite query that only performs a JOIN if a certain condition is met (items.super = 1). I know I could write some code in my application to figure out if super == 1 and then execute the correct query, but I'd much rather just have one query that works in both cases--whether super is 0 or 1.

SELECT i2.id, i2.name
FROM items i
JOIN items i2 ON i.subcategory_id = i2.subcategory_id AND i.super = 1
WHERE i.id = ?

Above, I tried to add a second condition to my JOIN clause, but that doesn't work when i.super == 0. Should I solve this with something wacky like two left joins and the coalesce() function?

Thanks.

Answer 1

This is how I read the question.

You want to show a record from items, the WHERE filter is applied to the item.
However, on occasion that the current item's super=1, you want to show all the sibling items instead of the same subcategory.

In that case, you can use this query

SELECT i2.id, i2.name
FROM items i
INNER JOIN items i2 ON
    (i.super = 0 and i2.id = i.id)
    OR
    (i.super = 1 and i.subcategory_id = i2.subcategory_id)
WHERE i.id = ?

Answer 2

Not sure if I understand correctly what you want.

SELECT i2.id, i2.name
  FROM items i
    JOIN items i2 
      ON i.subcategory_id = i2.subcategory_id 
        AND (i.super = 1 OR i.super = 0)
;