sqlmysqljoinsubquery

using two inner join in mysql query


users table:

enter image description here

offer_comments table:

enter image description here

offer_comments stores comments and answers to comments.

This gets comments and answers to comments depending on $id:

function getCommentsAnItem($id){    
    mysql_query("SET CHARACTER SET utf8");
    $result_comments = mysql_query("
        select e.comment as comment, m.comment as answer_to_comment 
        from offer_comments e 
        inner join offer_comments m on e.id = m.quet 
        where e.offer_id=$id and e.confirm=1");
    $comments = array();
    while($a_comment=mysql_fetch_object($result_comments)){
        $comment = array(
            'comment'=>$a_comment->comment,
            'answer'=>$a_comment->answer_to_comment
            );
        array_push($comments,$comment);
        }
    return $comments ;
    }

How can I use inner join instead of offer_comments?

I would like to use this SQL:

select offer.*, u.id, u.name, u.family
from offer_comments offer
inner join users u on offer.id=u.id

like:

    $result_comments = mysql_query("
        select e.comment as comment, m.comment as answer_to_comment 
        from (select offer.*, u.name, u.family
            from offer_comments offer
            inner join users u on offer.id=u.id
            ) e 
        inner join offer_comments m on e.id = m.quet 
        where e.offer_id=$id and e.confirm=1");

but it returns [].


Solution

  • Try this:

       $result_comments = mysql_query("select e.comment as comment,m.comment as answer_to_comment 
        from (select offer.*,u.name,u.family from offer_comments offer
        inner join users u on offer.user_id=u.id) e 
        inner join offer_comments m on e.id = m.quet 
        where e.offer_id=$id and e.confirm=1");
    

    The change here is in the e derived table the relation between users and offer_comments should be users.id = offer_comments.user_id and you've done it users.id = offer_comments.id