I want to write a select query to return data on only posts which have are associated with the taxonomy term
SELECT wp_postmeta . * , wp_posts.post_name FROM wp_posts, wp_postmeta, wp_terms, wp_term_relationships WHERE wp_posts.post_type = 'projects' AND wp_posts.post_status = 'publish' AND wp_posts.ID = wp_postmeta.post_id AND wp_term_relationships.object_id = wp_posts.ID AND wp_term_relationships.term_taxonomy_id = wp_terms.term_id AND wp_terms.slug = 'food-and-beverage' ORDER BY wp_posts.ID DESC
For some reason unknown to me, this is returning posts which do not have
As evidenced by the answer below, this is not the ideal approach, yet it is one that I've committed to, as refactoring the code (this is generated SQL, and theres lots more of it) would take me forever.
Since everyone seems to be in agreement (myself included) that the way I'm doing this is wrong, I'm going to reluctantly try to refactor.
However, I'm going to leave the question open for the time being in case someone can put together a quick fix for me (I'll happily drop you some bounty points).
For the bold/crazy, here is a gist with a big chunk of the source: https://gist.github.com/2950395
I pass the ajax it a couple "filters" as POST variables.