Problem with Self Join

[brandonBuster]

I'm working on a Wordpress project and since the wp_postmeta table saves each piece of data in its own record, I assume I'd need to perform a self join and a subquery to get the results I want.

I'm searching the DB for latitude/longitude coords per input location. I'd imagine this should work, but for some reason the WHERE clause isn't filtering anything...

SELECT lat.meta_value AS lt, lng.meta_value AS lg
FROM wp_postmeta AS lat
INNER JOIN wp_postmeta AS lng USING(post_id)
WHERE lat.post_id = (SELECT post_id FROM wp_postmeta WHERE meta_key = 'location_name' AND meta_value = 'some_sample_location' LIMIT 1)
AND lat.meta_key = '_jr_geo_latitude' OR lng.meta_key = '_jr_geo_longitude'

[r937]

SELECT lat.meta_value AS lt
     , lng.meta_value AS lg
  FROM wp_postmeta AS post
LEFT OUTER 
  JOIN wp_postmeta AS lat 
    ON lat.post_id = post.post_id
   AND lat.meta_key = '_jr_geo_latitude' 
LEFT OUTER
  JOIN wp_postmeta AS lng 
    ON lng.post_id = post.post_id
   AND lng.meta_key = '_jr_geo_longitude'
 WHERE meta_key = 'location_name' 
   AND meta_value = 'some_sample_location'

[brandonBuster]

Thanks. I forgot you can join a table on more than one condition.

I also forgot to mention that there will be duplicate entries per location, as multiple Wordpress posts might be linked to a single location (thanks to WP's crappy DB design). The reason for the subquery was so I could limit results to a single row.

I modified your suggested code, which is working much better. The WHERE clause is working now, but for some reason, even though I'm limiting the subquery results to a single post_id, I'm still getting multiple rows returned. I've confirmed my data isn't corrupted. The post_id/latitude and post_id/longitude combos are unique records.

SELECT lat.meta_value AS lt, lng.meta_value AS lg, post.post_id FROM wp_postmeta AS post
LEFT OUTER JOIN wp_postmeta AS lat ON lat.post_id = post.post_id AND lat.meta_key = '_jr_geo_latitude' 
LEFT OUTER JOIN wp_postmeta AS lng ON lng.post_id = post.post_id AND lng.meta_key = '_jr_geo_longitude'
WHERE post.post_id = (SELECT post_id FROM wp_postmeta WHERE meta_value = 'some_location' LIMIT 1)

[r937]

to debug your duplicate posts, run this --

SELECT *
  FROM wp_postmeta AS post
 WHERE post.post_id = 
      (SELECT post_id FROM wp_postmeta 
         WHERE meta_value = 'some_location' LIMIT 1)

[brandonBuster]

Hey Rudy, not sure I'm following. The data is fine. The duplicate entries for locations are necessary, as location is linked to more than one post in the wp_posts table. Unless I were to build another table to hold locations, there will always be multiple entries per location in the wp_postmeta table.

Right now I'm testing a very small dataset, having only four unique locations, and one location with two entries. With the SQL in my last post, I'm getting back the correct result, but always in multiples of 10. Locations with even just a single record in the DB are returning 10 sets of identical results.

[r937]

clearly there's an explanation, but i can't see it, because i can't see your data

if multiple posts have the same location, why would you search by location but then only return one post?

[brandonBuster]

Every post needs to be associated with a location, so when a new post comes in I need to set the coordinates for it. I'm doing this with the Google Maps API. But if a new post comes in for a location I already have a record for, I can use that rather than making the trip to Google. Given the design of the wp_posts table and the wp_postmeta table relationship, I only want to grab the first matching coordinates.

Rebuilding the query, I now have this. It seems to be doing the job I need it to.

SELECT lat.meta_value, lng.meta_value
FROM wp_postmeta lat
INNER JOIN wp_postmeta lng ON lat.post_id = lng.post_id AND lng.meta_key =  '_jr_geo_longitude'
WHERE lat.meta_key =  '_jr_geo_latitude'
AND lat.post_id = ( 
      SELECT post_id FROM wp_postmeta
      WHERE meta_key =  '_property_name'
      AND meta_value =  'The Charles Hotel Harvard Sq. Cambridge'
      LIMIT 1 )

[r937]

nice one

(well, except for the LIMIT in the subquery, but as long as it runs, you're okay)

i guess i didn't understand what you were doing