SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with Self Join

    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...

    Code:
    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'

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.



    Code:
    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)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    to debug your duplicate posts, run this --
    Code:
    SELECT *
      FROM wp_postmeta AS post
     WHERE post.post_id = 
          (SELECT post_id FROM wp_postmeta 
             WHERE meta_value = 'some_location' LIMIT 1)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Jun 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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 )

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •