distinct on union selects

[tyutghf]

I have a table with three category columns, a product may have one, two or three categories set.

My query is bring back a result for each so if for example

productid | productcategory | productcategory2 | roductcategory3 | otherstuff
1 | 5 | 3 | 6 |blah

this row will show up three times in my results as it is bringing it in for each category it finds. How do I distinct on union selects to make each one show up once only?

SQL = "SELECT shop_products.productid, shop_products.shop_producers_id, shop_products.productcategory, shop_products.productcategory2, shop_products.productcategory3, shop_products.productname, shop_products.productlongdesc, shop_products.productprice, shop_products.productsaleprice, shop_productscategory.productcategoryname FROM shop_products INNER JOIN shop_productscategory ON shop_products.productcategory=shop_productscategory.id where shop_products.visible='1' AND shop_products.shop_producers_id = '"&Request.Cookies("AdminId")&"'"

SQL = SQL & " UNION SELECT shop_products.productid, shop_products.shop_producers_id, shop_products.productcategory, shop_products.productcategory2, shop_products.productcategory3, shop_products.productname, shop_products.productlongdesc, shop_products.productprice, shop_products.productsaleprice, shop_productscategory.productcategoryname FROM shop_products INNER JOIN shop_productscategory ON shop_products.productcategory2=shop_productscategory.id where shop_products.visible='1' AND shop_products.shop_producers_id = '"&Request.Cookies("AdminId")&"'"

SQL = SQL & " UNION SELECT shop_products.productid, shop_products.shop_producers_id, shop_products.productcategory, shop_products.productcategory2, shop_products.productcategory3, shop_products.productname, shop_products.productlongdesc, shop_products.productprice, shop_products.productsaleprice, shop_productscategory.productcategoryname FROM shop_products INNER JOIN shop_productscategory ON shop_products.productcategory3=shop_productscategory.id where shop_products.visible='1' AND shop_products.shop_producers_id = '"&Request.Cookies("AdminId")&"'"

 

[r937]

remove the shop_products productcategory, productcategory2, and productcategory3 columns, as well as the shop_productscategory productcategoryname column from the SELECT clauses

vwalah, distinct rows



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[BitZero]

Hi - I'm not sure if this directly answers your question, but I would create a view that looks something like this:

SELECT productid, productcategory, productcategoryname
FROM
(
SELECT productid, productcategory
FROM shop_products
where productcategory > 0
UNION ALL
SELECT productid, productcategory2
FROM shop_products
where productcategory2 > 0
UNION ALL 
SELECT productid, productcategory3
FROM shop_products
where productcategory3 > 0
) AS cat 
INNER JOIN shop_productscategory AS nm ON cat.productcategory = nm.id
ORDER BY productid;

This view would have, for a given product id, a row for each non-zero category. If the view is called CategoryView, then your query would look like this:

SELECT shop_products.productid, shop_products.shop_producers_id, CategoryView.productcategory, CategoryView.productcategoryname, shop_products.productname, shop_products.productlongdesc, shop_products.productprice, shop_products.productsaleprice
FROM shop_products 
INNER JOIN CategoryView ON shop_products.productid = CategoryView.productid;

 

[BitZero]

Sorry - I left off the where statement at the very end:

where shop_products.visible='1' 
AND shop_products.shop_producers_id = '"&Request.Cookies("AdminId")&"'"

 

[tyutghf]

Sorry guys I just realised this is on mysql (I do usually use MS SQL). I have phpmyadmin to be able to administer the DB, whilst I can create views it won`t allow me to create a view with multiple from clauses so it doesn`t look like I can go down the view route although I have bookmarked the code for future reference.

r937, I need to call the product categories as I need to write them to the page

 

[NastiaMurzin]

Instead of using this statement three times in the following query:
<code>
"SELECT shop_products.productid, shop_products.shop_producers_id, shop_products.productcategory, shop_products.productcategory2, shop_products.productcategory3, shop_products.productname, shop_products.productlongdesc, shop_products.productprice, shop_products.productsaleprice, shop_productscategory.productcategoryname FROM shop_products INNER JOIN shop_productscategory ON shop_products.productcategory=shop_productscategory.id where shop_products.visible='1' AND shop_products.shop_producers_id =
'"&Request.Cookies("AdminId")&"'"

SQL = SQL & " UNION SELECT shop_products.productid, shop_products.shop_producers_id, shop_products.productcategory, shop_products.productcategory2, shop_products.productcategory3, shop_products.productname, shop_products.productlongdesc, shop_products.productprice, shop_products.productsaleprice, shop_productscategory.productcategoryname FROM shop_products INNER JOIN shop_productscategory ON shop_products.productcategory2=shop_productscategory.id where shop_products.visible='1' AND shop_products.shop_producers_id = '"&Request.Cookies("AdminId")&"'"

SQL = SQL & " UNION SELECT shop_products.productid, shop_products.shop_producers_id, shop_products.productcategory, shop_products.productcategory2, shop_products.productcategory3, shop_products.productname, shop_products.productlongdesc, shop_products.productprice, shop_products.productsaleprice, shop_productscategory.productcategoryname FROM shop_products INNER JOIN shop_productscategory ON shop_products.productcategory3=shop_productscategory.id where shop_products.visible='1' AND shop_products.shop_producers_id = '"&Request.Cookies("AdminId")&"'"

</code>
Use the code:
<code>
"SELECT shop_products.productid, shop_products.shop_producers_id, shop_products.productcategory, shop_products.productcategory2, shop_products.productcategory3, shop_products.productname, shop_products.productlongdesc, shop_products.productprice, shop_products.productsaleprice, shop_productscategory.productcategoryname FROM shop_products INNER JOIN shop_productscategory ON shop_products.productcategory=shop_productscategory.id where shop_products.visible='1' AND shop_products.shop_producers_id = '"&Request.Cookies("AdminId")&"'"
</code>

 

[r937]

I need to call the product categories

so if a product belongs to more than one category, won't you get the product more than once?


or did you want the count of the number of categories each product belongs to?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon