SQL Puzzler

[BlindPete]

Ok all you SQL gurus. I have two tables.
aTable contains a list if "item"s and there corresponding Hgh_range and Low_range values.
bTable contains a list of "value"s

What I need is a list of records from aTable that are not represented in bTable. I know I could right a loop and do this, but I wanted to do it in an SQL statement if possible.

Here is an SQL statement I tried, but VFP doesn't like my sub-query. If I run the sub-query by itself it works fine. I've tried several methods COUNT() EMPTY() Etc with no success.


SELECT * FROM aTable WHERE 0=RECCOUNT((SELECT item FROM bTable WHERE value >= aTable.low_range AND value <= aTable.hgh_range)) INTO CURSOR LookAtThese

-Pete

 

[TomasDill]

Hi!

Rewrite it by following way:

SELECT * FROM aTable WHERE NOT EXISTS (SELECT item FROM bTable WHERE value >= aTable.low_range AND value <= aTable.hgh_range) INTO CURSOR LookAtThese





Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softserve.lviv.ua

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[BlindPete]

Thanks Vlad, but that didn't work either. After much pondering the following did work...

SELECT DISTINCT item FROM aTable LEFT JOIN bTable ON ((bTable.value = aTable.low_range) AND (Table.value <= aTable.hgh_range)) WHERE EMPTY(bTable.value)


Thanks for trying!
-Pete