Page 1 of 1

[SOLVED] Problem with query

Posted: Tue Feb 21, 2006 2:37 am
by rsmarsha
I'm trying to pull out a list of products but exclude those already existing in another table. I have the following query:

Code: Select all

$list = "SELECT * FROM categories,products,form_products 
WHERE products.category_id=categories.category_id AND products.category_id='$cat' AND products.$p_type='1' 
AND products.product_id NOT IN (SELECT form_product FROM form_products WHERE form_id='$_POST[id]') ORDER BY products.product_id ASC";
Then a while loop to show the products. Without the sub query it works fine, just can't get the subquery to work. Any ideas?

Posted: Tue Feb 21, 2006 2:40 am
by feyd
look in Useful Posts (link in Forum Tour [signature]). I had a post on how to select records found in one table that weren't in another.

Posted: Tue Feb 21, 2006 3:10 am
by rsmarsha
Ok, tried :

Code: Select all

$list = "SELECT * FROM categories,products LEFT JOIN form_products ON products.product_id = form_products.form_product 
WHERE products.category_id=categories.category_id AND products.category_id='$cat' AND products.$p_type='1' 
ORDER BY products.product_id ASC";
And filtered with:

Code: Select all

if ($lrow['form_cat']!='NULL')
	{
But it's still showing products that are not in form_products.

Posted: Tue Feb 21, 2006 8:47 am
by feyd

Code: Select all

SELECT  gop_id, gop_option
FROM tbl_gradeoptions
LEFT JOIN tbl_customgrades ON gop_id = cgr_optionid AND cgr_classid = 33
WHERE  (cgr_optionid IS NULL)
is the query I was referencing.

Posted: Wed Feb 22, 2006 3:03 am
by rsmarsha
Thanks :) That seems to have done the trick.