Hi Lee,

This query does not work in SQLite but works in MS SQL Server:

sqlite> SELECT items_idx, [name], active FROM Items
   ...> WHERE active = 'T' AND Items.items_idx IN
   ...> ((SELECT related_item FROM RelatedItems WHERE item = 1777)
   ...> UNION
   ...> (SELECT item FROM RelatedItems WHERE related_item = 1777))
   ...> ORDER BY Items.name ASC;
SQL error: near "UNION": syntax error

Of course the problem is the brackets you have around each SELECT statement, which separates them from the UNION operator.

So, it works fine like this:

SELECT items_idx, [name], active FROM Items
WHERE active = 'T' AND Items.items_idx IN
(
    SELECT related_item FROM RelatedItems WHERE item = 1777
    UNION
    SELECT item FROM RelatedItems WHERE related_item = 1777
)
ORDER BY Items.name ASC;

Also, you may want to consider avoiding performing an IN on a UNION. As far as I know, SQLite doesn't optimize that, so will build the entire union before performing the IN. If you instead do the following, it should be a lot faster (if you have lots of data). But I may be wrong.

SELECT items_idx, [name], active
FROM Items
WHERE active = 'T'
    AND
        (
            items_idx IN
( SELECT [related_item] FROM RelatedItems WHERE item = 1777 )
            OR items_idx IN
( SELECT item FROM RelatedItems WHERE related_item = 1777 )
        )
ORDER BY [name] ASC
;


or by using a JOIN:

SELECT items_idx, [name], active
FROM Items
JOIN RelatedItems AS RI
WHERE active = 'T' AND
    (
        RI.related_item = Items.items_idx AND RI.item = 1777
        OR
        RI.item = Items.items_idx AND RI.related_item = 1777
    )
ORDER BY Items.name ASC
;

Tom


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------

Reply via email to