Select with two tables in MySQL

Question

I have two tables hits and posts. In hitstable I have id_post,hits and the poststable I have id,title

I need build the report with all posts with related hits and order by hits. Can anyone help me with select?

Answer 1

SELECT   posts.id as id, posts.title as title, hits.hits as hitsdate
FROM     posts INNER JOIN hits ON hits.id_post = posts.id
ORDER BY hits.hits

Answer 2

SELECT   posts.id, posts.title, hits.hits
FROM     posts LEFT JOIN hits ON hits.id_post = posts.id
ORDER BY hits.hits

Answer 3

try this

$q=mysql_query("SELECT * FROM `posts` ORDER BY `title`;");
while($qresult=mysql_fetch_array($q))
{
    $post_id=$qresult['id'];
    $post_title=$qresult['title'];
    $hitsretreiver=mysql_query("SELECT * FROM `hits` WHERE `id_post`='$post_id';")
    while($hitsretreiverresult=mysql_fetch_array($hitsretreiver)
    {
         $relatd_hit=$hitsretreiverresult['hits'];
         echo $post_title;
         echo $relatd_hit;
    }
 }

in this code you get every record in the table post and you get the record from table hits which has the same id