Tying 4 tables together using a Join

[jlipinski3]

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.

[jlipinski3]

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]

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]

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.

"wp_posts"
id, content

"wp_terms"
id, category_name

"wp_term_relationships" ties posts to categories
object_id (essentially wp_posts.id), category_id (essentially wp_terms.id)

"wp_postmeta"
custom_fieldname, custom_fieldvalue, content_id (essentially wp_posts.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]

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

i can do the first part for you...

SELECT wp_posts.id
     , wp_posts.content
  FROM wp_terms
INNER
  JOIN wp_term_relationships
    ON wp_term_relationships.category_id = wp_terms.id
INNER
  JOIN wp_posts
    ON wp_posts.id = wp_term_relationships.object_id
 WHERE wp_terms.category_name = 'home'

think you can add the remaining table?

[oddz]

(untested)

SELECT
      p.id
      ,p.content
  FROM
      wp_posts p
 INNER
  JOIN
      wp_term_relationships t2p
    ON
      p.id = t2p.object_id
 INNER
  JOIN
      wp_terms t
    ON
      t2p.category_id = t.id
  LEFT OUTER
  JOIN
      wp_postmeta m
    ON
     p.id = m.content_id
   AND
     m.custom_fieldname = 'slot'
 WHERE
      t.category_name = 'home'
 ORDER
    BY
      m.custom_fieldvalue

[jlipinski3]

i can do the first part for you...

SELECT wp_posts.id
     , wp_posts.content
  FROM wp_terms
INNER
  JOIN wp_term_relationships
    ON wp_term_relationships.category_id = wp_terms.id
INNER
  JOIN wp_posts
    ON wp_posts.id = wp_term_relationships.object_id
 WHERE wp_terms.category_name = 'home'

think you can add the remaining table?

Thanks r937, so far it's working great. I'm playing around with where to drop the final sorting, and what I've come up with


SELECT wp_posts.id, wp_posts.content
FROM wp_terms
INNER
JOIN wp_term_relationships
ON wp_term_relationships.category_id = wp_terms.id
INNER
JOIN wp_posts
ON wp_posts.id = wp_term_relationships.object_id
WHERE wp_terms.category_name = 'home'
INNER
JOIN wp_postmeta
ON wp_postmeta.content_id = wp_posts.ID WHERE wp_postmeta.custom_fieldname = 'slot'
ORDER BY wp_postmeta.custom_fieldvalue ASC

Swapping that final "WHERE" with "AND" doesn't do the trick either. What would I be better off doing to rethink the sorting logic?

[jlipinski3]

r937, nevermind, I GOT IT!

SELECT
wp_posts.ID,
wp_posts.post_content,
wp_postmeta.meta_value
FROM
wp_terms
INNER JOIN wp_term_relationships ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id)
INNER JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
WHERE
wp_terms.slug = 'home-slideshow-mouseovers' AND wp_postmeta.meta_key = 'slot'
ORDER BY
wp_postmeta.meta_value

Thank you for your help & for steering me in the right direction. I appreciate it hugely.

[r937]

...
INNER 
  JOIN wp_postmeta 
    ON wp_postmeta.post_id = wp_posts.ID
   AND wp_postmeta.meta_key = 'slot'
 WHERE wp_terms.slug = 'home-slideshow-mouseovers' 
ORDER 
    BY wp_postmeta.meta_value

also, please remove the parentheses you added to the ON conditions

[jlipinski3]

Thanks r937. Now what if I wanted to grab 2 values from the wp_postmeta table, both with the same post_id, but different meta_key.

Such as

SELECT
wp_posts.ID,
wp_posts.post_content,
wp_postmeta.meta_value as meta_value1
wp_postmeta.meta_value as meta_value2
FROM
wp_terms
INNER JOIN wp_term_relationships ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id)
INNER JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
WHERE
wp_terms.slug = 'home-slideshow-mouseovers' AND meta_value1 = 'slot' and meta_value2 = 'Read More Link'
ORDER BY
meta_value1

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]

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.

SELECT
wp_posts.ID,
wp_posts.post_content,
wp_postmeta.meta_value
FROM
wp_terms
INNER JOIN wp_term_relationships ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id)
INNER JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
WHERE
wp_terms.slug = 'home-slideshow-mouseovers' AND wp_postmeta.meta_value
= 'slot'
ORDER BY
wp_postmeta.meta_value

^ The above works fine.

It's just that the wp_postmeta table is going to contain data like this:

Table wp_postmeta
post_id meta_value meta_key
400 slot 1
401 slot 3
402 slot 2
400 Read More /news/400_1/
401 Read More /news/401_1/
402 Read More /news/402_1/

And I would like to query the wp_postmeta table for the meta_key under the meta_value of "Read More" while I query it for "slot".

I've tried designating the different value as some sort of alias, like below:

SELECT
wp_posts.ID,
wp_posts.post_content,
wp_postmeta.meta_value as meta_value1
wp_postmeta.meta_value as meta_value2
FROM
wp_terms
INNER JOIN wp_term_relationships ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id)
INNER JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
WHERE
wp_terms.slug = 'home-slideshow-mouseovers' AND meta_value1 = 'slot' and meta_value2 = 'Read More Link'
ORDER BY
meta_value1

It's just that something's off as far as my attempts at aliasing (in bold).

[r937]

SELECT wp_posts.ID
     , wp_posts.post_content
     , meta1.meta_value as meta_value1
     , meta2.meta_value as meta_value2
  FROM wp_terms
INNER 
  JOIN wp_term_relationships 
    ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
INNER 
  JOIN wp_posts 
    ON wp_posts.ID = wp_term_relationships.object_id
LEFT OUTER 
  JOIN wp_postmeta AS meta1
    ON meta1.post_id = wp_posts.ID
   AND meta1.meta_key = 'slot'
LEFT OUTER 
  JOIN wp_postmeta AS meta2
    ON meta2.post_id = wp_posts.ID
   AND meta2.meta_key = 'Read More Link'
 WHERE wp_terms.slug = 'home-slideshow-mouseovers'

i made them LEFT OUTER JOINs as i wasn't sure if you wanted them mandatory... change to INNER joins if you do

i also took off your ORDER BY clause because it no longer made sense, feel free to add it back with the appropriate column(s)

[jlipinski3]

This example is huge to help me get a better grasp on this stuff. It works perfectly, I added back in the ORDER BY. Thank you.