Help me with this sql query

March 21, 2009 11:14 PM

What's wrong with this sql query?

I'm trying to figure out why this query doesn't work. MySQL 4. I don't really know sql syntax well enough to figure out why this subquery business isn't working right. Thanks!

SELECT wp_term_relationships.object_id,
wp_term_relationships.term_taxonomy_id FROM wp_term_relationships WHERE
wp_term_relationships.object_id IN(
SELECT wp_term_relationships.object_id FROM wp_term_relationships WHERE
wp_term_relationships.term_taxonomy_id IN ( 0,3,4,5,6,7,8,9,10,11,1,12 )
AND wp_term_relationships.term_taxonomy_id NOT IN ( 0 )
) 
AND wp_term_relationships.term_taxonomy_id NOT IN(0,3,4,5,6,7,8,9,10,11,1,12) GROUP BY
wp_term_relationships.term_taxonomy_id;

posted by maniactown to Computers & Internet (3 answers total)

Subqueries were introduced in 4.1, not 4.0. Do you have 4.1?
posted by sbutler at 11:17 PM on March 21, 2009

Ugg... looking closer at that query, it will always return no results. Think about it. You're asking for this (psuedo-sql):

object_id IN (SELECT object_id FROM blah WHERE term_taxonomy_id IN( 3,4,5,6,7,8,9,10,11,1,12 )) AND
term_taxonomy_id NOT IN (0,3,4,5,6,7,8,9,10,11,1,12)

There's no way to satisfy that query! You're asking for all object_id that both DOES and DOESN'T have the same condition.
posted by sbutler at 11:26 PM on March 21, 2009

Thanks for your help. Turns out I was running an old 4.0 on my dev server and that was the cause of my problems. The mysql documentation groups 3.23, 4.0, and 4.1 all together and gave me the incorrect impression that subqueries were supported.

Incidentally, I didn't really care that the query returns no results since it's a php-generated query... sometimes it will generate results... I just needed a valid query. Thanks again.
posted by maniactown at 2:42 AM on March 22, 2009