Jump to content

Filtering by multiple categories in the same row


whitt

Recommended Posts

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   

Link to comment
Share on other sites

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    |
          +-----+-----------+---------+           +----+---------+---------+
Link to comment
Share on other sites

@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 by Psycho
Link to comment
Share on other sites

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
Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.