Filtering by multiple categories in the same row

[whitt]

Im struggling with how to filter items that have multiple categorises say if i wanted to search for a shoe that is good for walking and hiking in my database?

Shoe_id
Shoe name
color_id

type_id

colors

color_id

color

 

type

type_id

type

My problem is what do i do in a situations like this? say a shoe is good for walking and running? aka type 1 and 2 

shoe id 1 shoe name nike color id 1 type id 1 and 2   

[Barand]

The answer is "data normalization"

http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360

 

So if

shoe 1 is available in brown and suitable for hiking and walking

shoe 2 is available in brown and white and suitable for running

color                       shoe                            type
+----------+-------+        +---------+-----------+         +---------+-----------+
| color_id | color |        | shoe_id | shoe_name |         | type_id |   type    |
+----------|-------+        +---------+-----------+         +---------+-----------+
|    1     | brown |        |    1    | Nike      |         |    1    | Walking   |
|    2     | white |        |    2    | Adidas    |         |    2    | Running   |
+----------+-------+        +---------+-----------+         |    3    | Hiking    |
     |                         |    |                       +---------+-----------+
     |                         |    |                               |
     +--------------+          |    +----------------------+        |                 
                    |          |                           |        |
     shoe_color     |          |              shoe_type    |        |
          +-----+-----------+---------+           +----+---------+---------+
          |  id | color_id  | shoe_id |           | id | shoe_id | type_id |
          +-----+-----------+---------+           +----+---------+---------+
          |  1  |    1      |    1    |           |  1 |     1   |    1    |
          |  2  |    1      |    2    |           |  2 |     1   |    3    |
          |  3  |    2      |    2    |           |  3 |     2   |    2    |
          +-----+-----------+---------+           +----+---------+---------+

[Psycho]

@Barand,

 

Maybe it's because I haven't finished my coffee this morning. I know the schema is valid. But, going back to the OPs initial statement I don't know how the query would be efficiently created to return shows that match multiple "types". If we JOIN the shoe_types table on the shoe table there would be an individual record for each shoe/type combination. But, since we are looking for a shoe that matches two types, would we need to JOIN on the shoe_type table twice? It would seem unwieldy the more parameters that are trying to be matched. I'm guessing there is a strait forward method that I am not connecting the dots on.

Edited December 9, 2014 by Psycho

[Barand]

Perhaps

SELECT 
  s.shoe_id
, s.shoe_name
FROM shoe s
INNER JOIN
    (
    SELECT shoe_id
    FROM shoe_type
    WHERE type_id IN (1,3)
    ) t USING (shoe_id)
GROUP BY shoe_id
HAVING COUNT(*) = 2;

[Psycho]

Yeah, I had thought about using count. Seems like an awkward method since I assume the number of Types would be variable.

 

But, is the sub-query required in that statement. Would this work

 

SELECT s.shoe_id, s.shoe_name
FROM shoe s
JOIN shoe_type st
  ON s.shoe_id = st.shoe_id
  AND st.type_id IN (1,3)
GROUP BY shoe_id
WHERE COUNT(s.shoe_id) = 2

[mikosiko]

 

Yeah, I had thought about using count. Seems like an awkward method since I assume the number of Types would be variable.

 

But, is the sub-query required in that statement. Would this work

SELECT s.shoe_id, s.shoe_name
FROM shoe s
JOIN shoe_type st
  ON s.shoe_id = st.shoe_id
  AND st.type_id IN (1,3)
GROUP BY shoe_id
WHERE COUNT(s.shoe_id) = 2

you mean HAVING COUNT(s.shoe_id) = 2 right?.... sure was a typo

[Barand]

You're right, the subquery is superfluous.