[SOLVED] Problem with query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

[SOLVED] Problem with query

Post 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?
Last edited by rsmarsha on Wed Feb 22, 2006 3:03 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post by rsmarsha »

Thanks :) That seems to have done the trick.
Post Reply