Select a field value with various possible combinations
I want to select a field value in another possible combination containing item1,2,3,4,5. It doesn't matter where the element is, but each field must contain each element.
id_num field1 field2 field3 field4 field5
1 item1 item2 item3 item4 item5 > true
2 item3 item3 item3 item4 item5 > false
3 item2 item1 item3 item4 item5 > true
4 item5 item4 item3 item2 item1 > true
5 item1 item1 item3 item4 item5 > false
6 item2 item2 item4 item4 item5 > false
I tried to use a normal query SELECT * from tb_name where field1=item1 AND field2=item2 etc...
, it must consume a lot OR OR OR OR, it must not be very efficient.
thank
+3
source to share
2 answers
You can use a function field
to make your query a little simpler than just a bunch and
and or
s:
SELECT *
FROM tb_name
WHERE FIELD ('item1', field1, field2, field3, field4, field5) > 0 AND
FIELD ('item2', field1, field2, field3, field4, field5) > 0 AND
FIELD ('item3', field1, field2, field3, field4, field5) > 0 AND
FIELD ('item4', field1, field2, field3, field4, field5) > 0 AND
FIELD ('item5', field1, field2, field3, field4, field5) > 0
+3
source to share
Try using concat etc.
select concat(field1,field2,field3,field4,field5) as checkstring,item_id from table where
concat(field1,field2,field3,field4,field5) like '%item1%'
and concat(field1,field2,field3,field4,field5) like '%item2%'
and concat(field1,field2,field3,field4,field5) like '%item3%'
and concat(field1,field2,field3,field4,field5) like '%item4%'
and concat(field1,field2,field3,field4,field5) like '%item5%'
Update.
+2
source to share