Results 1 to 3 of 3

Thread: Comparing 3 Tables

  1. #1
    Join Date
    Jan 2008
    Posts
    42
    Thanks
    2
    Thanked 1 Time in 1 Post

    Default Comparing 3 Tables

    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:

    PHP Code:
    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_idint(11NOT NULL auto_increment,
    `
    vendor_idint(11NOT NULL default '0',
    `
    product_parent_idint(11NOT NULL default '0',
    `
    product_skuvarchar(64NOT NULL default '',
    `
    product_s_descvarchar(255) default NULL,
    `
    product_desctext,
    `
    product_thumb_imagevarchar(255) default NULL,
    `
    product_full_imagevarchar(255) default NULL,
    `
    product_publishchar(1) default NULL,
    `
    product_weightdecimal(10,4) default NULL,
    `
    product_weight_uomvarchar(32) default 'pounds.',
    `
    product_lengthdecimal(10,4) default NULL,
    `
    product_widthdecimal(10,4) default NULL,
    `
    product_heightdecimal(10,4) default NULL,
    `
    product_lwh_uomvarchar(32) default 'inches',
    `
    product_urlvarchar(255) default NULL,
    `
    product_in_stockint(11NOT NULL default '0',
    `
    product_available_dateint(11) default NULL,
    `
    product_availabilityvarchar(56NOT NULL default '',
    `
    product_specialchar(1) default NULL,
    `
    product_discount_idint(11) default NULL,
    `
    ship_code_idint(11) default NULL,
    `
    cdateint(11) default NULL,
    `
    mdateint(11) default NULL,
    `
    product_namevarchar(64) default NULL,
    `
    product_salesint(11NOT NULL default '0',
    `
    attributetext,
    `
    custom_attributetext NOT NULL,
    `
    product_tax_idint(11) default NULL,
    `
    product_unitvarchar(32) default NULL,
    `
    product_packagingint(11) default NULL,
    `
    child_optionsvarchar(45) default NULL,
    `
    quantity_optionsvarchar(45) default NULL,
    `
    child_option_idsvarchar(45) default NULL,
    `
    product_order_levelsvarchar(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.

    PHP Code:
    $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:

    PHP Code:
    $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') "
    PHP Code:
    $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.

  2. #2
    Join Date
    Jan 2008
    Posts
    42
    Thanks
    2
    Thanked 1 Time in 1 Post

    Default

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

    PHP Code:
    $q .= ",product_category_xref AS PC, product AS P "
    Then you add this to the end of the WHERE part:

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

  3. #3
    Join Date
    Aug 2009
    Posts
    398
    Thanks
    42
    Thanked 4 Times in 4 Posts

    Default

    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..

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •