MySQL Help in Wordpress (Basic, I think but not sure!)

0
votes

How can I find distinct Property values within a table based on their Area value and their post_id must match? Let's say I want Property values for Arabian Ranches based on the below table. The result I'm looking for is Avenida 1, Avenida 2.

post_id 104908, meta_key Property, meta_value Avenida 1
post_id 104908, meta_key Area, Arabian Ranches
post_id 104909, meta_key Property, meta_value Avenida 2
post_id 104909, meta_key Area, Arabian Ranches
post_id 104910, meta_key Property, meta_value Al Arta 1
post_id 104910, meta_key Area, Greens
post_id 104911, meta_key Property, meta_value Avenida 2
post_id 104911, meta_key Area, Arabian Ranches

We know which Property and which Area belong together based on their rows having matching post_ID's.

1
mysqlwordpress

1 Answers

0
votes

Asuming you don't mind having therm displayesdin a nice comma separated list:

SELECT meta_key, GROUP_CONCAT( DISTINCT meta_value SEPARATOR ', ' ) AS meta_values FROM wp_postmeta GROUP BY meta_key HAVING meta_key LIKE '%Area%';

This will produce something like this:

meta_key | meta_values
------------------------------------------------
Area 1   | Value 1, Value 2
Area 51  | Aliens, More Aliens, Even more aliens

If you don't want it groupped, you can always return separate rows:

SELECT DISTINCTROW meta_key, meta_value FROM wp_postmeta WHERE meta_key LIKE '%a%' ORDER BY meta_key, meta_value;

Which will produce

meta_key  | meta_value
----------------------
Area 1    | Value 1
Area 2    | Value 2
Area 51   | Aliens
Area 51   | More aliens

Hope this helps.