Sorry for the long post, please bear with me.
I have a table setup like this:
-------------------------------------------------
| id | SKU | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 | Q8 |
-------------------------------------------------
| 1 | 1001 | 4 | 8 | 10 | 3 | 1 | 9 | 2 | 1 |
| 2 | 1001 | 3 | 6 | | | | | 1 | 4 |
| 3 | 1001 | 2 | 2 | | | | | | 3 |
| 4 | 1001 | 1 | | | | | | | |
---------------------------------------------------
Q1..Q8 store the id of an answer (stored in a separate table) for each of the 10 questions. I am getting data from the user into a 8 element array, which stores the id of the answer they chose. I need to write a query that finds an exact product match based on the answers the chose. This isn't a problem - until you realize that there can be multiple rows for each product (see above), which says that there are multiple answers for a particular question. If the users chooses answers 4,8,10,3,1,9,2,1 (which corresponds with the first in the above table), they will get a result (SELECT * FROM table WHERE Q1='4', Q2='8', Q3='10 and so on). The problem is, 3,8,10,3,1,9,2,1 needs to also show a result (because Q1 can be 4 OR 3 OR 2 OR 1) and so on. I need help writing a query that will satisfy this (if it is even possible). If this is not possible, any help redesigning this application would be very appreciated. Thanks.
I have a table setup like this:
-------------------------------------------------
| id | SKU | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 | Q8 |
-------------------------------------------------
| 1 | 1001 | 4 | 8 | 10 | 3 | 1 | 9 | 2 | 1 |
| 2 | 1001 | 3 | 6 | | | | | 1 | 4 |
| 3 | 1001 | 2 | 2 | | | | | | 3 |
| 4 | 1001 | 1 | | | | | | | |
---------------------------------------------------
Q1..Q8 store the id of an answer (stored in a separate table) for each of the 10 questions. I am getting data from the user into a 8 element array, which stores the id of the answer they chose. I need to write a query that finds an exact product match based on the answers the chose. This isn't a problem - until you realize that there can be multiple rows for each product (see above), which says that there are multiple answers for a particular question. If the users chooses answers 4,8,10,3,1,9,2,1 (which corresponds with the first in the above table), they will get a result (SELECT * FROM table WHERE Q1='4', Q2='8', Q3='10 and so on). The problem is, 3,8,10,3,1,9,2,1 needs to also show a result (because Q1 can be 4 OR 3 OR 2 OR 1) and so on. I need help writing a query that will satisfy this (if it is even possible). If this is not possible, any help redesigning this application would be very appreciated. Thanks.