A Query with several value combinations

[Eliasen]

Hi everyone!

 

I am not sure if this is possible, but can you make a query where it checks for several combinations? Eg. instead of making a query like this:

$value_a = 5;
$value_b = 1;
$value_c = 9; 

"SELECT * FROM table WHERE       row_1 = '$value_a ' AND row_2 = '$value_b ' AND row_3 = '$value_c '     OR     row_1 = '$value_c ' AND row_2 = '$value_a ' AND row_3 = '$value_b '     OR     row_1 = '$value_b ' AND row_2 = '$value_c ' AND row_3 = '$value_a ' .... etc etc etc."

 

Is there a way to do this in a more fancy, quicker way, instead of having to put in all the different combinations?

 

I hope I made sense, else please let me know and I will try to elaborate.

 

Thanks!

[The Little Guy]

im not sure what row is supposed to be, but

 

select * from table_name where column_name in(1,2,3,4,5);

 

The above will select everything from "table_name" where column_name equals 1, 2, 3, 4, or 5

[Eliasen]

Thanks for the quick reply!

 

However it won't do exactly what I am after, as I don't want the values to be used more than once for each combination, eg:

$a = banana

$b = apple

$c = orange

 

so if

$row_1 = '$a', $row_2 and 3 may not be assigned $a, (not the value, but the variable, like if $b = banana, $b may be assigned to either row_2 or 3).

 

Phew, I am not that good at explaining, however I hope I made somewhat sense heh.

 

Thanks again!

[Eliasen]

Sorry for the double post.

 

The variables in the above example is the ingredients, and it check in the database what you can make with the ingredient combination.

So like if the database I have looks like this:

 

| Recipe Name | Ingredient_1 | Ingredient_2 | Ingredient_3 |

-----------------------------------------------------------------------------

| Sample Cake |  Banana        |    Orange      | Apple            |

| Sample Cake |  Orange        |    Apple          | Banna            |

-----------------------------------------------------------------------------

 

the query should spit out both of them, even though the combination is not the same, but they both contain the same ingredients.

[ebmigue]

IMO, your table schema ought to be changed to:

 

`Recipe Name` VARCHAR(30)
`Ingredient` VARCHAR(30)

 

Then uniquely indexed with the fields Recipe Name and Ingredient (i.e., a composite candidate key.)

 

That way, you can query the recipe that contains a given set of ingredients easily.

 

If am to write a query for what I think is you want as results, it will be very long, and difficult to maintain, especially for recipes

that have more than 3 ingredients.

 

Hope it helps.

[Eliasen]

Thank you very much for you reply!

 

I am still very new at SQL/PHP, any chance you could elaborate on what you just said, as I have no idea how to do this.

 

Yes I did think there had to be a better way than to build a very long query.

 

Thanks again!

[ebmigue]

I'm sorry that I could not truly help you on this. Your problem concerns database normalization, and is best understood only if studied seriously.

Of course, what level of normalization in your case is applicable, you alone could determine based on the specifications of your program.

 

You can google "database normalization." For a start, IMO, "1-to-many" relationship between relvars/tables is applicable in your case.

 

Good luck and hope it helps.

[Eliasen]

Thank you very much , I will get started reading.

 

Thanks!

[ebmigue]

I would recommend a book by Chris Date, titled An Introduction to Database Systems, 8e.

 

His experience and competence in the field is without a doubt among the top.

[Eliasen]

Noted. Thanks again

[Muddy_Funster]

in the interim using brackets should get you the desired effect:

...WHERE ((row_1 = '$value_a ' AND row_2 = '$value_b ' AND row_3 = '$value_c ') OR (row_1 = '$value_c ' AND row_2 = '$value_a ' AND row_3 = '$value_b ') OR (row_1 = '$value_b ' AND row_2 = '$value_c ' AND row_3 = '$value_a ')....( etc etc etc.))"