Only join tables if a certain condition is met

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.

---

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 = ?

---

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)
;

---

Isn't what you're after:

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))
WHERE i.id = ?

?

---

I'm with everyone else who's not sure if they understand the question, but since you're asking about coalesce(), I think one left join would be sufficient:

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

Alternatively, use one procedure

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

And let your application decide which columns to use after the query is run, rather than before.