CODEWITHSUNDEEP
mysqlsql
InspiredCoder
InspiredCoder

Reputation: 404

How to select rows which can have a given value in multiple columns

I am working on an inventory management system. I have provided 5 fields for user to enter product category. Now I am rather confused How do I select rows which can have same product category in any of the five columns.

The schema is similar to 

 id, name, size, cat1, cat2,cat3,cat4,cat5

What I want for example is that I want to select all items that have product category "Clothes", it should into all five columns and return me all distinct rows. How can I do that?

Thank you so much.

Upvotes: 2

Views: 44

Answers (1)

trincot
trincot

Reputation: 350147

Your database should have been designed differently, where you would have the categories associated to a product stored in a separate table.

In the current model, you could make your query like this:

SELECT id, name, size
FROM   product
WHERE  "Clothes" IN (cat1, cat2, cat3, cat4, cat5)

If you have another category to check for, like "Footwear", then it depends whether you are looking for products that have both categories or one of both:

  1. Products that have both "Clothes" and "Footwear" categories (AND):

    WHERE  "Clothes" IN (cat1, cat2, cat3, cat4, cat5)
AND    "Footwear" IN (cat1, cat2, cat3, cat4, cat5)

  2. Products that have at least one of both categories (OR):

    WHERE  "Clothes" IN (cat1, cat2, cat3, cat4, cat5)
OR     "Footwear" IN (cat1, cat2, cat3, cat4, cat5)

Creating a view

You could consider creating a view to make it easier to make such, and more complex, queries:

CREATE VIEW product_cat (product_id, cat) AS
    SELECT id, cat1 FROM product UNION
    SELECT id, cat2 FROM product UNION
    SELECT id, cat3 FROM product UNION
    SELECT id, cat4 FROM product UNION
    SELECT id, cat5 FROM product;

And then you can do queries like:

SELECT     product.id, product.name, product.size, product_cat.cat
FROM       product
INNER JOIN product_cat ON product.id = product_cat.product_id
WHERE      product.cat LIKE 'C%';

This would list products with their categories, but only those categories that start with a "C".

Upvotes: 2

Related Questions