SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problems Adding JOIN to Custom Select Query

    Hi,
    I realised today that some of the events that should have been showing up on a website weren't, and eventually realised the reason was because that 'term_taxonomy_id' in the 'wp_term_relationships' table is not always the same value as the category_id. Strangely those two fields matched for the first year the site was running but have now fallen out of sync. So, I know need to match the category_id to the terms_id field in the 'wp_term_taxonomy' table, which is linked to the 'wp_term_relationships' table via the 'term_taxonomy_id' field. Does that all make sense? That was just the intro!

    So, I've tried adding an extra join to my custom select query but now it's returning no values. I've tried all kinds of different joins but still no luck. Does anyone know what I'm doing wrong? Here's the SQL query below, which includes the extra LEFT JOIN for this new field.

    Code:
    $category_ID = get_category_id(single_cat_title("", false));
    $sql = "SELECT post_id, 
    DATE_FORMAT(start,'%m/%d/%Y') AS eventStart, 
    DATE_FORMAT(end,'%m/%d/%Y') AS eventEnd,
    DATE_FORMAT(CURDATE(),'%m/%d/%Y') AS today,
    DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 7 DAY),'%m/%d/%Y') AS endWeek 
    	FROM   wp_ec3_schedule s
    	JOIN   wp_posts p ON p.ID = s.post_id
    	JOIN   wp_term_relationships t ON t.object_id = s.post_id
    	LEFT JOIN wp_term_taxonomy x ON x.term_taxonomy_id = t.term_taxonomy_id
    	WHERE   post_status = 'publish' AND term_taxonomy_id = $category_ID AND end > CURDATE()";
    $sql .= ' ORDER BY start ASC';
    $sql .=	' LIMIT 0, 10';
    Once I have this query working I was then going to change 'term_taxonomy_id' to 'term_id' in my WHERE clause, which should then bring all the correct values. But for now, I just want to get some records back. What's wrong with my query?

    Cheers,
    Russ

  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)
    Quote Originally Posted by slaterino View Post
    What's wrong with my query?
    test the query without the last join (the LEFT join) and confirm that it returns appropriate rows

    if adding the join to wp_term_taxonomy results in no rows, that can only mean one thing -- there are no rows in the wp_term_taxonomy table which match on the join condition you provided

    please note that the query as written won't run, because the term_taxonomy_id in the WHERE clause is ambiguous
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's why I was getting any results! Blooming ambiguity! So, the JOIN was fine but it was because the term_taxonomy_id was ambiguous that the problem was being caused. Cheers for sorting that one out!

    Quote Originally Posted by r937 View Post
    test the query without the last join (the LEFT join) and confirm that it returns appropriate rows

    if adding the join to wp_term_taxonomy results in no rows, that can only mean one thing -- there are no rows in the wp_term_taxonomy table which match on the join condition you provided

    please note that the query as written won't run, because the term_taxonomy_id in the WHERE clause is ambiguous


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
  •