Comparing 3 Tables

[jfreak53]

Ok I have tried a bunch of different things and can no way get this query to work right in any way at all. I have tried sub queries and I have tried joins and no go. I have this system that pulls categories from a table in DB. But on the command line I am passing it now a "vendor_id" parameter. I grab this parameter and am able to pass this is not the problem. The problem is this. There are two tables besides the category table. There is product, and then product_category_xref. The first one is the list of products, and in this table there is a field called, vendor_id. Then the second table has three fields, this table is to tell the system which category each product belongs to. What I want to do is at the end of the select statement that already exists in the system, I want to have it read through the products list and only display those categories that have products registered to X vendor. Here is the printout of the tables so you know what is there:

category` (
`category_id` int(11) NOT NULL auto_increment,
`vendor_id` int(11) NOT NULL default '0',
`category_name` varchar(128) NOT NULL default '',
`category_description` text,
`category_thumb_image` varchar(255) default NULL,
`category_full_image` varchar(255) default NULL,
`category_publish` char(1) default NULL,
`cdate` int(11) default NULL,
`mdate` int(11) default NULL,
`category_browsepage` varchar(255) NOT NULL default 'browse_1',
`products_per_row` tinyint(2) NOT NULL default '1',
`category_flypage` varchar(255) default NULL,
`list_order` int(11) default NULL,
PRIMARY KEY (`category_id`)

product` (
`product_id` int(11) NOT NULL auto_increment,
`vendor_id` int(11) NOT NULL default '0',
`product_parent_id` int(11) NOT NULL default '0',
`product_sku` varchar(64) NOT NULL default '',
`product_s_desc` varchar(255) default NULL,
`product_desc` text,
`product_thumb_image` varchar(255) default NULL,
`product_full_image` varchar(255) default NULL,
`product_publish` char(1) default NULL,
`product_weight` decimal(10,4) default NULL,
`product_weight_uom` varchar(32) default 'pounds.',
`product_length` decimal(10,4) default NULL,
`product_width` decimal(10,4) default NULL,
`product_height` decimal(10,4) default NULL,
`product_lwh_uom` varchar(32) default 'inches',
`product_url` varchar(255) default NULL,
`product_in_stock` int(11) NOT NULL default '0',
`product_available_date` int(11) default NULL,
`product_availability` varchar(56) NOT NULL default '',
`product_special` char(1) default NULL,
`product_discount_id` int(11) default NULL,
`ship_code_id` int(11) default NULL,
`cdate` int(11) default NULL,
`mdate` int(11) default NULL,
`product_name` varchar(64) default NULL,
`product_sales` int(11) NOT NULL default '0',
`attribute` text,
`custom_attribute` text NOT NULL,
`product_tax_id` int(11) default NULL,
`product_unit` varchar(32) default NULL,
`product_packaging` int(11) default NULL,
`child_options` varchar(45) default NULL,
`quantity_options` varchar(45) default NULL,
`child_option_ids` varchar(45) default NULL,
`product_order_levels` varchar(45) default NULL,
PRIMARY KEY (`product_id`)

product_category_xref` (
`category_id` int(11) NOT NULL default '0',
`product_id` int(11) NOT NULL default '0',
`product_list` int(11) default NULL 

Now this is the current SQL statement, this is fine it works just fine.

$q = "SELECT category_id, category_thumb_image, category_child_id,category_name FROM category,category_xref "; 
$q .= "WHERE category_xref.category_parent_id='$category_id' "; 
$q .= "AND category.category_id=category_xref.category_child_id "; 
$q .= "AND category.vendor_id='$ps_vendor_id' "; 
$q .= "AND category.category_publish='Y' "; 

What I want to do is at the end add another AND statement to filter out those categories that do not have a product in them from the currently selected vendor. I have tried these two statements and no go at all:

$q .= "AND $vendor_id IN (SELECT C.category_id, C.category_name 
FROM category AS C, product_category_xref AS PC, product AS P 
WHERE C.category_id = PC.category_id 
AND PC.product_id = P.product_id 
AND P.vendor_id = '$vendor_id') "; 

$q .= "AND $vendor_id IN (SELECT P.vendor_id 
FROM product AS P, category AS C, product_category_xref AS PC 
WHERE C.category_id = PC.category_id 
AND PC.product_id = P.product_id 
AND P.vendor_id = '$vendor_id') "; 

And of course neither one of them worked. Any ideas as to what I am doing wrong and how I can make this filter work?

Thanks in advance for any help in this matter.

[jfreak53]

Ok just got it, the code to do this if someone else is ever interested is. You add these two tables to the FROM:

$q .= ",product_category_xref AS PC, product AS P "; 

Then you add this to the end of the WHERE part:

$q .= "AND category.category_id = PC.category_id ";
$q .= "AND PC.product_id = P.product_id ";
$q .= "AND P.vendor_id = $vendor_id "; 

[auriaks]

Im new to this topic and it seems interesting... Can someone explain what is all about?? I think it is really valuable thing for auto table creating..