If I have a table like this:
ID color size weight
1 red 2 3
2 green 4 5
So to run a mysql query to find the ID number that is color:red and size:2 and weight:3 I can do this:
select ID from table where color=red AND size=2 AND weight=3
As properties are growing in addition to color, weight, size, mileage, speed, etc... and I want to keep the table scaling it would make sense to organize it this way
ID ID2 property value
1 1 color red
2 1 size 2
3 1 weight 3
4 2 color green
5 2 size 4
6 2 weight 5
How do I run a select query here to find the ID number that is color:red and size:2 and weight:3
Should I create a bunch of self joins like suggested here:
select t.id2
from test t
join test t1 on(t.id = t1.ID2)
join test t2 on(t.id = t2.ID2)
where t.property = 'color'
and t1.property = 'size'
and t2.property = 'weight'
and t.value = 'red'
and t1.value = '2'
and t2.value = '3'
or should I just grab all the ID2s from the database and sort through in with PHP?
Or is there any other way to select the data from the table?
I am looking at 10,000 IDs and about 200 properties that will very slowly grow in number.
Here is another way to get the desired result set if you are not going to use bunch of self joins then you can use in(),count(distinct) and group by and i assume for each id2 you have unique properties
select t.id2
from test t
where t.property in('color','size','weight')
and t.value in('red','2','3')
group by t.id2
having count(distinct t.property) = 3
Above query will look for property column for provided values and count with distinct = 3 part will ensure for all the provided key/value pair to match,So if your properties grow from 3 then you have to provide the no. which will be equal to the no. of properties to match i.e for case t.property in('color','size','weight','test') the count part will change to having count(distinct t.property) = 4