how to join two tables together to get result

[radsp]

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:

[Barand]

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

[radsp]

No Still i am abble to fetch only those posts which are having meta key.....i want all posts which are having meta key and which are not having...only difference is that sort by meta value.

[Barand]

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

[radsp]

oh.....!!!! Thanks a lot guru.....u made my day ...really big thanks ......... it works fine as per my requirement .