SQL JOIN在同一个表上有2个条件

Question

I need to combine 2 columns into 1 row. The key between the columns is the post_id.

Here is my table (postmeta):

post_id     meta_key    meta_value
36          cp_state    California
37          cp_state    Illinois
38          cp_state    California
39          cp_state    California
40          cp_state    California
36          cp_city     Los Angeles
37          cp_city     Chicago
38          cp_city     San Diego
39          cp_city     San Diego
40          cp_city     Los Angeles

And here is what im trying to output:

post_id     state       city
36          California  Los Angeles
37          Illinois    Chicago
38          California  San Diego
39          California  San Diego
40          California  Los Angeles

Here is the SQL i used but get a crazy amount of results:

SELECT a.post_id AS pID, a.meta_value AS state, b.meta_value AS city
FROM $wpdb->prefix"."postmeta AS a
LEFT JOIN $wpdb->prefix"."postmeta AS b
ON b.post_id = a.post_id
WHERE a.meta_key = 'cp_state'
AND b.meta_key = 'cp_city'

In the live Wordpress table im using, there's obviously hundreds of other meta_key, so i need it to filter just for these 2 meta_key (cp_city, cp_state)

Answer 1

You should use INNER JOIN instead of LEFT JOIN

SELECT a.post_id AS pID, a.meta_value AS state, b.meta_value AS city
FROM       $wpdb->prefix"."postmeta AS a
INNER JOIN $wpdb->prefix"."postmeta AS b
ON b.post_id = a.post_id
WHERE a.meta_key = 'cp_state'
AND b.meta_key = 'cp_city'