Need help with sql query.

Question

There is a table (wp_postmeta), the structure is as follows:

id post_id key value 1 11 fromcity_1 Москва 2 11 fromdistrict_1 Садовое кольцо 3 11 fromcity_2 Воронеж 4 11 fromdistrict_1 Коминтерновский 5 13 fromcity_2 Воронеж 6 13 fromdistrict_1 Северный

How can I get post_id , where %fromcity_% = Москва , and %fromdistrict_% = Садовое кольцо ?

EAV-style: select p. Post from postmeta pm1 on p.id = pm1.post_id and pm1.key like 'fromcity_%' join postmeta pm2 on p.id = pm2.post_id and pm2.key like 'fromdistrict_ % 'where pm1.value =' Moscow 'and pm2.value =' Garden Ring ';

Vitalina eleven one 2 eight · Accepted Answer · 2014-10-25T14:59:15

Without subqueries and wild joins it will not work, probably:

 SELECT post_id FROM wp_postmeta AS p WHERE p.`key` LIKE 'fromcity_%' AND p.`value` = 'Москва' AND EXISTS ( SELECT 1 FROM wp_postmeta AS c WHERE c.post_id = p.post_id AND c.`key` LIKE `fromdistrict_%` AND c.`value` = 'Садовое кольцо' )

@RattleSneyk here it is important to answer two questions: 1. What exactly will happen if there is a subquery?
I just thought it was possible to shorten the request somehow =)
dependent subquery will lead to the fact that for each post from Moscow a query will be executed by districts.
Well, if it happens that fromcity_1 and fromcity_2 both will be Moscow, we will get doubles, therefore at least distinct.

msi msi 11k eleven sixteen · Answer 2 · 2014-10-25T19:06:03

 SELECT post_id FROM wp_postmeta AS p WHERE (p.`key` LIKE 'fromcity_%' AND p.`value` = 'Москва') OR (p.`key` LIKE `fromdistrict_%` AND p.`value` = 'Садовое кольцо') group by post_id having count(*)=2

here having count(*)>=2 also necessary, users can break everything.

Need help with sql query.

2 answers 2

More articles: