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

[agentforte]

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:

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

[r937]

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

[agentforte]

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.

[agentforte]

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)

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`;