Re^3: nested dbi queries question

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

Comment on Re^3: nested dbi queries question

Replies are listed 'Best First'.
Re^4: nested dbi queries question by VSarkiss (Monsignor) on Feb 02, 2005 at 16:39 UTC
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 )` [download] I wrote the same thing in my reply below, but didn't point it out specifically. Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^4: nested dbi queries question
by VSarkiss (Monsignor) on Feb 02, 2005 at 16:39 UTC

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
)
[download]

Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.

[reply]
[d/l]
[select]