I suggest you change your INNER JOINs to LEFT JOINs. INNER JOIN will remove rows if nothing matches in the table to the left side of the join.
You may also want to recast your ON clauses with parentheses, as follows:
INNER JOIN wp_term_taxonomy ttpa
ON ( ttpa.taxonomy='pa_pa'
AND ttpa.term_taxonomy_id=relationships.term_taxonomy_id)
edit
What purpose does the GROUP BY clause serve? You are using an evil confusing nonstandard MySQL extension to GROUP BY. Read this and understand it, or get rid of GROUP BY. http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html Seriously.
There are three things going on in your query, it seems. We need to break this stuff down carefully if we are to have any chance of troubleshooting this thing. Let's use the Structured in Structured Query Language to get this working correctly.
First, you are trying to get a resultset containing particular posts (in your case, items for sale).
SELECT p.id, p.post_title, p.guid
FROM wp_posts p
WHERE p.post_status = 'publish'
AND post_type = 'product'
Does this query yield an appropriate list of items? It may contain some unpriced items or other such things, but it should have, at least, all the items you need in your list.
Second, you are trying to retrieve and display taxonomy information (categories, keywords, etc) for your product posts. Here's a query that fetches that stuff, I think (I don't understand this part of your application as well as you do). I think there are two areas of taxonomy you're trying to pull out. This is the typical WordPress taxonomy lookup hairball. One of them is this:
SELECT tr.object_id AS id,
t.name AS mozrank
FROM wp_term_relationships AS tr
INNER JOIN wp_term_taxonomy AS x
ON (x.taxonomy='pa_mozrank'
AND x.term_taxonomy_id=tr.term_taxonomy_id)
INNER JOIN wp_terms AS t
ON t.term_id=x.term_id
Test this query. It should give a row for every post that's classified in the 'pa_mozrank' taxonomy showing the post id and the mozrank value in that taxonomy. Make sure this query works. If not, figure it out. Do not proceed until you understand this one and have it working.
Ditto ditto for the the query to fetch the value from the pa_pa hierarchy.
SELECT tr.object_id AS id,
t.name AS pa
FROM wp_term_relationships AS tr
INNER JOIN wp_term_taxonomy AS x
ON (x.taxonomy='pa_pa'
AND x.term_taxonomy_id=tr.term_taxonomy_id)
INNER JOIN wp_terms AS t
ON t.term_id=x.term_id
Third, you're retrieving particular attributes from post_meta. We need something similar for posts and prices, and posts and stock status. Again, write these queries and debug them individually. Here's for price.
SELECT post_id AS id, meta_value AS price
FROM wp_postmeta
WHERE meta_key = `_regular_price'
Here's for stock status.
SELECT post_id AS id, meta_value AS stockstatus
FROM wp_postmeta
WHERE meta_key = `_stock_status'
Good: we have five debugged subqueries. Each is indexed on id.
- list of published product posts
- list of all posts with their pa_mozrank classifications.
- list of all posts with their pa_pa classifications.
- price.
- stocking status.
Now we need to join all this stuff together. Here's the general outline of our final query. Presumably you can see how this relates to your original query.
SELECT whatever
FROM wp_posts AS p
LEFT JOIN (mozranks) AS mo ON p.id = mo.id
LEFT JOIN (pas) AS pa ON p.id = pa.id
LEFT JOIN (prices) AS pr ON p.id = pr.id
LEFT JOIN (stockstatus) AS ss ON p.id = ss.id
WHERE p.post_status = 'publish'
AND p.post_type = 'product'
AND pr.price <> ''
AND ss.stockstatus = 'instock'
We're using LEFT JOIN here because we still want stuff from the wp_posts table even if it is missing some or all of the attributes.
Finally, putting it all together, we get rather a gigantic query. But we get a query in which all the parts of been unit tested. So, we don't have to smack our foreheads and say WTF? WTF? at this stage of creating the query.
SELECT whatever
FROM wp_posts AS p
LEFT JOIN (
SELECT tr.object_id AS id,
t.name AS mozrank
FROM wp_term_relationships AS tr
INNER JOIN wp_term_taxonomy AS x
ON (x.taxonomy='pa_mozrank'
AND x.term_taxonomy_id=tr.term_taxonomy_id)
INNER JOIN wp_terms AS t
ON t.term_id=x.term_id
) AS mo ON p.id = mo.id
LEFT JOIN (
SELECT tr.object_id AS id,
t.name AS pa
FROM wp_term_relationships AS tr
INNER JOIN wp_term_taxonomy AS x
ON (x.taxonomy='pa_pa'
AND x.term_taxonomy_id=tr.term_taxonomy_id)
INNER JOIN wp_terms AS t
ON t.term_id=x.term_id
) AS pa ON p.id = pa.id
LEFT JOIN (
SELECT post_id AS id, meta_value AS price
FROM wp_postmeta
WHERE meta_key = `_regular_price'
) AS pr ON p.id = pr.id
LEFT JOIN (
SELECT post_id AS id, meta_value AS stockstatus
FROM wp_postmeta
WHERE meta_key = `_stock_status'
) AS ss ON p.id = ss.id
WHERE p.post_status = 'publish'
AND p.post_type = 'product'
AND pr.price <> ''
AND ss.stockstatus = 'instock'
That's a long query, but you can see how it's put together pretty easily, and you can debug the parts one by one.
Query optimizers are designed to make this stuff as efficient as it can be, so you don't have to worry about that, unless you have, let's say, millions of items for sale, in which case you're probably amazon.com anyway.
If you were an enterprise developer doing this for a big company, you might use SQL views for the subqueries. (If you were on Oracle or PostgreSQL, you could employ WITH clauses in your queries.) You could even do this in WordPress, but you'd probably want to write a plugin that created and deleted them on activation and deactivation. Just using large SQL queries might be easier.
