DISTINCT Question

[phpknight]

Hi,

 

I have a table in which I have two similar fields:  category_ID1 and category_ID2.  A user is allowed to pick up to two categories in which to classify an item.  I'm trying to get get a list of distinct categories present in the table with no duplicates. So, if category_ID1 has 1 2 5 6 7, and category_ID2 has 2 3 4 5, I want to get one field of results that simply has the numbers 1-7 in it with no duplicate rows.  I'm sure this is possible, but I've been unable to figure it out.  Any help would be appreciated.

 

[aschk]

I'm taking a guess that the table you are talking about is a "products" table...

 

You NEED to normalise your information. Your categories should be stored in a separate table, thus enabling you to do "SELECT * FROM categories", which will give you the information you require. However, if you're adamant that you require more than 1 category for product then you need a 3rd table which provides the mapping between the 2 (hence mimicking the mutli-multi relationship).

 

Give us your table layout, sample data, and sample output and we'll give you some advice.

[phpknight]

I had a feeling that there would be normalization issues when I let products get two categories.  There is a categories table by itself but the category_ID1 and category_ID2 are stores in the products table.  What design would you suggest?  I was thinking about that mapping stuff, but I thought I would ask here first. 

 

BTW, the reason I need to even do this is that I don't want to display any empty categories on the webpage, only ones with products.

[aschk]

I propose a 3 table design, with the following tables, with some sample entries included:

(bear in mind it's a sample layout so you'll probably want to add your own columns)

Products
----------------------------------
id | name           | price | currency
====================================
1  | kitchen knife  | 9.99  | GBP
2  | kettle         | 5.99  | USD

note: i threw in a currency parameter, don't ask me why, just came in a flash of inspiration...

Categories
-----------------
id | name
=================
1  | electronics
2  | kitchenware


Product_Categories
------------------------
product_id | category_id
========================
1          | 2
2          | 2
2          | 1

 

Now, you'll be needing some queries to get all the information back out.

 

1) Getting a product with category names (using subselect + JOINs), e.g. getting kettle by id

SELECT p.name as 'product'
      ,(SELECT GROUP_CONCAT(c.name SEPARATOR '|')
        FROM categories c
        JOIN product_categories pc ON c.id = pc.category_id
        WHERE pc.product_id = p.id
       ) as 'categories'
      ,p.price as 'price'
FROM products p
WHERE p.id = 2

 

note: in the above the categories will be in 1 column ONLY and will be pipe (|) separated.

 

2) Getting products based on category (id), e.g. all kitchenware

SELECT p.name
FROM product_categories pc
JOIN products p ON pc.product_id = c.id
WHERE pc.category_id = 2

 

note: in the above, you don't need to use the categories table because you're working with id. If however you wish to search by category name then you need to use the categories table.

 

3) Finding the number of products based on category (id) , e.g. kitchenware (2)

SELECT COUNT(pc.product_id) as 'number'
FROM product_categories pc
WHERE pc.category_id = 2
GROUP BY pc.category_id

 

4) Finding the number of products based on category (name) , e.g. "kitchenware"

SELECT COUNT(pc.product_id) as 'number'
FROM product_categories pc
JOIN categories c ON pc.category_id = c.id
WHERE c.name = 'kitchenware'
GROUP BY pc.category_id

 

Hope that helps you get a headstart.

[phpknight]

Okay, thanks for the input.  Actually, I ended up just making a separate field that says usingThisCategory.  It is marked with a 1 if it is, and then I just pull those.  I can update that every week or two, and that should do the job.  But I do appreciate the input.