Jump to content

how to join two tables together to get result


radsp

Recommended Posts

Please someone help me out..!!

 

im using wordpress in that i want to display posts order by meta value, but my code gives me only posts which are having meta value. but i want all posts which are having meta value and which are not, only difference is need to show all posts above which are having meta value. following is my code :

 

$query= mysql_query("SELECT m.meta_key,m.meta_value,c.ID,c.post_date,c.post_title,c.post_name,c.post_content,c.post_author,u.object_id,u.term_taxonomy_id FROM wp_posts c, wp_term_relationships u,wp_postmeta m WHERE c.ID=u.object_id and u.term_taxonomy_id='$taxonomy' and m.meta_key='priority' and m.post_id=c.ID and post_status='publish' order by meta_value DESC") or die(mysql_error());

 

:confused: :confused:

Link to comment
Share on other sites

Sounds like a LEFT JOIN is required for the meta table

SELECT m.meta_key
    ,m.meta_value
    ,c.ID
    ,c.post_date
    ,c.post_title
    ,c.post_name
    ,c.post_content
    ,c.post_author
    ,u.object_id
    ,u.term_taxonomy_id 
FROM wp_posts c
    INNER JOIN 
    wp_term_relationships u ON c.ID=u.object_id
    LEFT JOIN 
    wp_postmeta m ON m.post_id=c.ID
WHERE  u.term_taxonomy_id='$taxonomy' 
        AND m.meta_key='priority' 
        AND post_status='publish' 
ORDER BY meta_value DESC
Link to comment
Share on other sites

Slight change required to the WHERE and the LEFT JOIN condition

SELECT m.meta_key
 ,m.meta_value
 ,c.ID
 ,c.post_date
 ,c.post_title
 ,c.post_name
 ,c.post_content
 ,c.post_author
 ,u.object_id
 ,u.term_taxonomy_id
FROM wp_posts c
  INNER JOIN
  wp_term_relationships u ON c.ID=u.object_id
  LEFT JOIN
  wp_postmeta m ON m.post_id=c.ID AND m.meta_key='priority'
WHERE u.term_taxonomy_id='$taxonomy'
  AND post_status='publish'
ORDER BY meta_value DESC
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.