SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict agentforte's Avatar
    Join Date
    May 2007
    Location
    Toronto, ON, Canada
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How do I select columns (1-1-1),(1-null-2),(1-2-null)

    Hello,

    I have four tables:
    1- manufacturer (ID, name)
    2- product_family (ID, mfg_id, cat_id, name)
    3- product (ID, mfg_id, family_id, cat_id, name)
    4- categories(ID, name)

    Note that a family might not have any products (i.e. no rows in the product table where product.family_id == product_family.ID)

    Also a product might not have a family (product.family_id == null)

    Category is optional and added based on the product or product family (using an if statement to check both)

    I want to select the following in one query, but I am not getting null for the product_id or product_family_id where I expected it, instead I get too many columns.

    manufacturer_id, product_family_id, product_id

    I am trying to get:

    1,1,null
    1,2,null
    1,3,null
    1,4,1
    1,4,2
    1,null,3
    1,null,4

    where product family 1, 2, and 3 do not have a product under them, product family 4 has 2 products
    products 3 and 4 are not assigned to a family.

    instead I get something like:

    1,1,1
    1,1,2
    1,1,3
    1,2,1
    1,2,2
    1,2,3
    1,3,1
    1,3,2
    1,3,3
    1,4,1
    etc.


    here is my code, and I am not sure how to narrow my results:


    Code:
    SELECT
    
     mfg.ID as `mfg_id`
    ,mfg.name as `mfg_name`
    
    ,products_family.ID as `family_id`
    ,products_family.name as `family_name`
    
    ,products.ID as `product_id`
    ,products.name as `product_name`
    
    , products_categories.ID
    , products_categories.name
    
    FROM mfg
    
    LEFT JOIN products_family ON mfg.ID = products_family.principal_id
    
    LEFT JOIN products ON mfg.ID = products.principal_id
    
    LEFT JOIN products_categories ON (products_categories.ID = if(products.ID is not null ,products.cat_id ,products_family.cat_id) )
    
    WHERE mfg.ID = 1
    
    ORDER BY products_family.name, products.name
    Last edited by agentforte; Jan 5, 2009 at 16:54. Reason: to make question more clear

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i looked at this a couple of times

    your relationships are redundant and confusing, and your column names are inconsistent between your table descriptions and your query

    what you will likely need is several queries, for the different "paths" throuogh the relationships, combined in a UNION

    but it's beyond me at the moment
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict agentforte's Avatar
    Join Date
    May 2007
    Location
    Toronto, ON, Canada
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for the reply r937.

    I think this requires 3 separate queries (maybe a UNION will work, I never used one before, so I will look into this)

    Basically, I wanted to get 3 types of results:

    1) manufacturer, product family, category
    2) manufacturer, product, category
    3) manufacturer, product family, product, category

    I was trying to get all of these results in one query, then use a PHP loop to display links to each product family or product based on manufacturer or category.

  4. #4
    SitePoint Addict agentforte's Avatar
    Join Date
    May 2007
    Location
    Toronto, ON, Canada
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb solved: one query, no union, using if statements

    EDIT: A union is needed. The code below returns the right number of rows (which might be a fluke) but the if statements place "null" in the columns, so this is not useful. The correct way would be to make 3 select statements with a UNION to merge them into one query.



    So I got the result I was looking for.
    I hope this is useful to anyone else looking to get this kind of relationship:

    table1.id in table2 and table3
    table2.id in table 3
    table4.id in table2 and table3

    This basically allows someone to get results for something that can be categorized in more than one way, for example both products and product families can be categorized by manufacturer OR category.

    The "if" statements narrow results so products not assigned to a family have family columns showing "null" and product families that do not have products have product columns showing "null". For example:

    manufacturer1, family1, product1, cat1
    manufacturer1, null, product2, cat2
    manufacturer1, family2, null, cat2

    where product1 belongs to family1, product 2 does not belong to a family and family2 has no specific product entries.

    (note: the above lists results for manufacturer1
    It can be easily modified to list results for category1)

    Code:
    SELECT 
    	 principals.ID as `company_id`
    	,principals.company_name as `company`
    
    	,if(products_family.ID = products.product_family_id
    	, products_family.ID, null) as `family_id`
    	,if(products_family.ID = products.product_family_id
    	, products_family.name,null) as `family_name`
    
    	,if(products_family.ID = products.product_family_id
    	, products.ID,null) as `product_id`
    	,if(products_family.ID = products.product_family_id
    	, products.product_family_id,null) as `product_family_id`
    	,if(products_family.ID = products.product_family_id
    	, products.name,null) as `product_name`
    
    	, products_categories.ID as `category_id`
    	, products_categories.cat_name as `category`
    
      FROM principals, products_family, products, products_categories
    
      WHERE principals.ID = 1
    
      AND products_family.ID is not null
      AND principals.ID = products_family.principal_id
    
      AND products.product_family_id is not null
      AND principals.ID = products.principal_id
    
      AND (products_categories.ID = products.primary_cat_id
    		OR	products_categories.ID = products_family.primary_cat_id)
    
      ORDER BY `product_name`, `family_name`;
    Last edited by agentforte; Jan 6, 2009 at 12:54. Reason: Explaining error found in code


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
  •