Basic SQL queries are no problem for me, but the Joins and complex ORDER BY isn't easy for me.
There's 4 tables, and they are part of a Wordpress setup.
What I'm trying to do is summed up in 4 SELECT statements:
1. SELECT guid FROM wp_posts WHERE id=(JOIN category_inclusions) AS main_query ORDER BY (JOIN lineup) ASC
2. SELECT term_id FROM wp_terms WHERE slug='home-slideshow-mouseovers' AS category_ids
3. SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id=(JOIN category_ids) AS category_inclusions
4. SELECT meta_value FROM wp_postmeta WHERE metakey='slot' AND post_id='(JOIN category_inclusions)' AS lineup
The summation of it is this:
QUERY1's IDs match where=QUERY3's category_ids match where QUERY2's rows include "home-slideshow-mouseovers", and sort it all according to what "slot" value QUERY1's IDs have in the "wp_postmeta" table.
It's complicated, but seems perfect for some good JOIN syntax. I've played w/different query creators, but it's a tough task even knowing what should take priority.