Ordering the added rows from a LEFT OUTER JOIN

Databases
1

In MySQL, is there any way to order what gets added to the query through a LEFT JOIN?

This big fat query:

SELECT wp_posts.ID, wp_posts.post_content, wp_posts.post_status, wp_posts.guid, wp_posts.post_title, wp_postmeta.meta_value, wp_terms.name, wp_terms.slug, posts_img.guid AS image_guid, posts_img.post_modified 

FROM wp_terms 

INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_id = wp_terms.term_id AND wp_term_taxonomy.taxonomy = 'category' 

INNER JOIN wp_term_relationships ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id

INNER JOIN wp_posts ON wp_posts.ID = wp_term_relationships.object_id AND wp_posts.post_status = 'publish'

LEFT OUTER JOIN wp_posts AS posts_img ON posts_img.post_parent=wp_posts.ID AND posts_img.post_type='attachment'

LEFT OUTER JOIN wp_postmeta on wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_key='slot' WHERE wp_terms.slug = 'in-the-spotlight' 

GROUP BY wp_posts.ID ORDER BY ISNULL(wp_postmeta.meta_value), wp_postmeta.meta_value ASC, posts_img.post_modified DESC

What I want to do is add “LEFT OUTER JOIN wp_posts AS posts_img ON posts_img.post_parent=wp_posts.ID AND posts_img.post_type=‘attachment’ ORDER BY posts_img.post_modified DESC” to that left outer join.

If there is any way to order what is returned/added from a LEFT OUTER JOIN, would any sharp SQL mind point me in the right direction. I appreciate it!

2

there is only one way to order the rows produced by a query, and that’s using an ORDER BY clause

and there is only one ORDER BY clause allowed in each query, although it may have multiple columns

3

Thanks r937. I’ve figured out that if I don’t GROUP BY, then I can order them all using the post_img.post_modified . But if use the GROUP BY, then it stunts my ORDER BY, and for some reason I get a value in post_img.guid that is an older value, and what I don’t want.

So in GROUP BY, it must be grabbing the bottom-most row of the added rows, for some reason.

4

why are you even using GROUP BY ???

5

Without GROUP BY it returns multiple rows of the same ID, with the same content, just because of different LEFT JOIN’ed post_img.

6

so you have multiple attachments per post, but you dont want to show all of them

which one do you want to show? presumably you want to show at least one of them, otherwise you wouldn’t even join to them…