4 ms·
You would need to have a new join for each new property SELECT DISTINCT loyaltyMemberID from members as m INNER JOIN properties as p1 on m.id = p1.
by any626 8y ago
You would need to have a new join for each new property
SELECT DISTINCT loyaltyMemberID
from members as m
INNER JOIN properties as p1 on m.id = p1.user_id
INNER JOIN properties as p2 on m.id = p2.user_id
INNER JOIN properties as p3 on m.id = p3.user_id
AND (p1.prop = 'gender' AND p1.value = x)
AND ((p2.prop = 'age' AND p2.value = y) OR (p3.prop = 'censor' AND p3.value = z))
- pbnjay 8y agoEspecially with partial indexes, I still feel like this structure will be significantly faster than the original UNION ALL ... GROUP BY on calculated fields. And they mention in the post that most queries don't use that many fields.
- TimJYoung 8y agoThere's no need for the extra joins, you can just do the one join and then filter everything in the WHERE clause: SELECT DISTINCT loyaltyMemberID from members as m INNER JOIN properties as p on m.id=p.user_id WHERE (p.prop='name' AND p.value = value) AND ...etc.
- 83457 8y agoBut how would you do exclusions with your approach?
- TimJYoung 8y agoI'm not sure what you're asking - could you give me an example of what you're envisioning that couldn't be satisfied with a combination of Boolean expressions in the WHERE clause ?
- 83457 8y agoWith this query approach how do you find people that have a prop1 but do not a prop2? If you get records back with prop1 then you have to remove those records from results based on another record. There are multiple ways to accomplish this but it can't be done with a single join and simple where clause.
- TimJYoung 8y agoSorry, I missed your reply. Yes, you are correct, in that case you would need to use a except, sub-query, derived table, etc.