Re^3: nested dbi queries question

[philosophia]

i think i've narrows the problem to this line
$sql = "SELECT * FROM sp WHERE NOT EXISTS (SELECT * FROM SP,SUPPLIER WHERE SUPPLIER.snum = SP.snum)";
it's not finding any results. it's supposed to find all rows in table sp where sp.snum does not exist in table supplier

[VSarkiss]

The problem is that your subselect is not correlated. In other words, you need to join sp in the main query to supplier in the subquery -- don't join the tables in the subquery. Specifically, the query should be:

SELECT *
FROM sp
WHERE NOT EXISTS (
    SELECT 1
    FROM supplier     -- note SP doesn't appear here!
    WHERE supplier.num = sp.num  -- sp is from main query
)

I wrote the same thing in my reply below, but didn't point it out specifically.