SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Threaded View

  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 15:54. Reason: to make question more clear


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
  •