Ggtcf

I am trying to get an SQL statement for wordpress to work by grouping different meta columns.

I have one table with votes, one table with locations and one table with reference links to some cities (Each city can contain multiple locations).

I am trying to group all votes based on each big city, but I am having some trouble putting the two SQL statements into one.

Here is the query to get the top 50 locations in terms of number of votes:

SELECT Count(*) as Amount, meta.meta_value as Location FROM wp_votes as votes
LEFT JOIN wp_postmeta as meta ON votes.`post_id` = meta.`post_id`
WHERE meta.meta_key = 'location'
AND votes.`post_id` IS NOT NULL AND votes.`post_id` <> 0
GROUP BY meta.meta_value
ORDER BY Amount DESC
LIMIT 50;

This returns a response like this:

|---------------------|------------------|
| Amount | meta_value |
|---------------------|------------------|
| 1250 | Brooklyn |
|---------------------|------------------|
| 400 | Manhattan |
|---------------------|------------------|
| 300 | Chicago |

And here is the query to get the Cities based on the location. It returns a list of cities (in the example above it would just return New York) and the two locations "Brooklyn", "Manhattan" would be "included" in New York.

SELECT meta_value FROM wp_posts as location
LEFT JOIN wp_postmeta as city ON city.`post_id` = location.`ID`
WHERE city.`meta_key` = 'reference'
GROUP BY city.`meta_value`;

The idea here is to map these two queries together, so that if there is a match in location from the first query and the location in the second query, it would group the rows by the city name instead.

So the output for the combined queries would be:

|---------------------|------------------|
| Amount | meta_value |
|---------------------|------------------|
| 1650 | New York |
|---------------------|------------------|
| 300 | Chicago |
|---------------------|------------------|

Is this possible?

Just include a second JOIN to get the reference:

SELECT c.meta_value as city, Count(*) as Amount 
FROM wp_votes v LEFT JOIN
 wp_postmeta l
 ON v.`post_id` = l.`post_id` AND
 l.meta_key = 'location' LEFT JOIN
 wp_postmeta c
 ON l.id = c.`post_id` AND
 c.meta_key = 'city'
WHERE v.`post_id` IS NOT NULL AND v.`post_id` <> 0
GROUP BY c.meta_value
ORDER BY Amount DESC
LIMIT 50;