Problems Adding JOIN to Custom Select Query

CMS & WordPress
1

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.

$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

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

3

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!