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]
-----------------------------------------------------------------------------