View Full Version : Normalized data querying

09-23-2011, 11:32 AM
obligatory_data1 means data that has to be there

properties are optional


id obligatory_data1
1 A
2 B
3 C


id properties
1 P1
1 P4
1 P5
2 P1

in order to find id that has P1 and P4 property

& get one id per resulting row
& get obligatory_data1

that is want to get result based on condition P1,P4 -->

id obligatory_data1
1 A

VARIANT1(bad, tested, slow):

select m.id,m.obligatory_data1, group_concat(d.properties)
from master m left join detail d on m.id = d.id
group by m.id
having group_concat(d.properties) like,regexp(something complicated here) or use of locate((...

VARIANT2(join on select):

select m.id,m.obligatory_data1 from master m1 left join
select distinct m.id
from master m left join detail d on m.id = d.id
where d.properties = 'P1' or d.properties = 'P4'
) as d1
om m1.id = d1.id

Any better idea ?
Comments, suggestions ?