SELECT question

[knelson]

hello-

is it possible to issue a single SELECT request in MySQL that will look for a value in multiple columns of the same table? For example...search for a match of '$item_number' in a table 'numbers' in columns '$item1', '$item2', and so on?

thank you very very much..

knelson

[steved]

Sure

Select * from myTable
where item1 = $item_number
or item2 = $item_number
or item3 = $item_number

[knelson]

thank you. unfortunately, though, now I'm returning all records, and not just the desired ones.

SELECT *
FROM tabl31, table2		
WHERE '$query_item_number' = t2_item1
OR '$query_item_number' = t2_item2
OR '$query_item_number' = t2_item3
OR '$query_item_number' = t2_item4
OR '$query_item_number' = t2_item5
OR '$query_item_number' = t2_item6
OR '$query_item_number' = t2_item7
OR '$query_item_number' = t2_item8
OR '$query_item_number' = t2_item9
OR '$query_item_number' = t2_item10
AND t1_season = '$season'
AND t2_fk = t1_id
GROUP BY t1_shot_name

The deal: table1 holds information about a photo shoot; table2 holds item numbers and color codes for each item number in that shoot. The item number + color code combination is unique, hence the multiple rows of the same type. Very un-3rd-normal-form. Anyway, the query above is giving me all the records in table1.

thanks again...

kurt

[knelson]

just as an fyi...i don't think that the OR operator is well supported in MySQL. I continue to get unpredictable results as soon as I include more than one table in my SELECT statement.

thanks for looking..

knelson

[khaldryck]

maybe with () your query will work better ...
didn't try though.

SELECT *FROM tabl31, table2        

WHERE ($query_item_number' = t2_item1
OR '$query_item_number' = t2_item2
OR '$query_item_number' = t2_item3
OR '$query_item_number' = t2_item4
OR '$query_item_number' = t2_item5
OR '$query_item_number' = t2_item6
OR '$query_item_number' = t2_item7
OR '$query_item_number' = t2_item8
OR '$query_item_number' = t2_item9
OR '$query_item_number' = t2_item10)
AND t1_season = '$season' 
AND t2_fk = t1_id
GROUP BY t1_shot_name

it should return you items for only the $season and at least one one the $query_item_number.

i don't really understand what you really want though, so i can't help more.

[knelson]

thank you!

it works now.

knelson