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:

SELECT guid FROM wp_posts WHERE id=(JOIN category_inclusions) AS main_query ORDER BY (JOIN lineup) ASC

SELECT term_id FROM wp_terms WHERE slug='home-slideshow-mouseovers' AS category_ids

SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id=(JOIN category_ids) AS category_inclusions

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.

jlipinski3
—
2010-11-29T16:23:46Z —
#2

To simplify this up a bit & just to get me some traction on this, say I removed a condition.

I'm just looking for the JOIN syntax to do this:

SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id=(SELECT term_id FROM wp_terms WHERE slug='home-slideshow-mouseovers') AS category_inclusions

And then to ORDER BY, then the category_inclusions have to be JOIN'ed somehow.

ORDER BY (SELECT meta_value FROM wp_postmeta WHERE metakey='slot' AND post_id='(JOIN category_inclusions)' AS lineup

When I look at different examples, the queries get tricky for me when "ON" is used. The masterminds of SQL would think this is cake, the same as how I could point out the best practices to use w/PHP, so the help is appreciated.

r937
—
2010-11-29T16:41:36Z —
#3

jlipinski3 said:

The masterminds of SQL would think this is cake...

nope

the reason your first post went unanswered, and the second one is likely to have the same result, is because it's not at all clear what you're trying to do

nor did you explain what the tables contain, or how they are related

jlipinski3
—
2010-11-29T18:01:30Z —
#4

Thanks r937. I'm trying to explain this the best way that I can, so here's putting it in more conversational English.

There's 4 tables:

"wp_posts" contains rows of content."wp_terms" is a table of categories."wp_term_relationships" ties categories by their id in wp_terms to their object_id in wp_posts."wp_postmeta" is a table containing custom fields and their values, tied to a record in wp_posts by id.

So what I'm trying to do is tie everything together, and putting it in simple english, "I'm trying to select all posts of a certain category and order them according to a custom field called 'slot'."

Which would come up like this:

SELECT id, content FROM wp_posts WHERE id=(JOIN: SELECT object_id's FROM wp_term_relationships WHERE category_id=(JOIN: SELECT id's FROM wp_terms WHERE category_name="home")) ORDER BY (JOIN SELECT wp_postmeta.fieldvalue WHERE wp_postmeta.content_id=wp_posts.id AND wp_postmeta.fieldname="slot")

It just looks like a mess when it's typed out like that.

r937
—
2010-11-29T18:15:23Z —
#5

jlipinski3 said:

"I'm trying to select all posts of a certain category and order them according to a custom field called 'slot'."

It'd seem to me that iterating through that result using mysql_result("query", $x, "meta_value1") to spit out the 'slot' value and mysql_result("query", $x, "meta_value2") to spit out the 'Read More Link' value would be logical. Does it seem like there would be any problem or smarter way to do this, to you?

jlipinski3
—
2010-11-30T21:15:26Z —
#11

I have been working on using a JOIN to create a pool of data across 3 different tables, and to sort it according to "slot" values in another table. The query works great. Now I'm looking to return data in the rows "wp_postmeta.meta_value" as some sort of alias that I can call later when I iterate through the result set.