Select SQL Query with Alias and Join

2 Views

I’m working on a query for 2 days now, but I don’t know how to make it.

Basically I’m doing a Query using multiple join:

Read More

My Query : 

SELECT wp_posts.ID, wp_posts.post_title, wp_posts.post_content, wp_posts.post_type,     
wp_term_taxonomy.taxonomy, wp_terms.name
FROM wp_term_relationships, wp_term_taxonomy, wp_terms, wp_posts
WHERE wp_term_relationships.object_id =80
AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
AND wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id
AND wp_term_relationships.object_id = wp_posts.ID

That’s what she display (on the top), and what I wanna display (bottom)

http://tinyurl.com/cnjmuxs

Many thanks for your help

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. Maybe someone else has a cleaner way to do this, but the following should work:

    SELECT wp_posts.ID, wp_posts.post_title, wp_posts.post_content, wp_posts.post_type, 
    MAX(
        CASE WHEN wp_term_taxonomy.taxonomy = 'reference' THEN wp_terms.name ELSE '' END 
    ) AS reference,
    MAX(
        CASE WHEN wp_term_taxonomy.taxonomy = 'chinesename' THEN wp_terms.name ELSE '' END 
    ) AS chinesename,
    MAX(
        CASE WHEN wp_term_taxonomy.taxonomy = 'price' THEN wp_terms.name ELSE '' END 
    ) AS price
FROM wp_term_relationships, wp_term_taxonomy, wp_terms, wp_posts
WHERE wp_term_relationships.object_id =80
    AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    AND wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id
    AND wp_term_relationships.object_id = wp_posts.ID
GROUP BY wp_posts.ID, wp_posts.post_title, wp_posts.post_content, wp_posts.post_type