wawan Setiyawan wawan Setiyawan - 1 month ago 10
MySQL Question

inner join two foreign key in one table

i have query sql for searching. i have two table to be inner join. this my table :



table_order
------------
id_order id_user_order id_user_taker status
1 2 3 done
2 2 4 done

table_user
--------------
id_user name
1 Alex
2 Jason
3 Danis
4 Lorent





i will inner join like this :



<?php
include'conn.php'
$qry = $conn->query("select table_order.*, table_user.name AS order_name, table_user.name AS taker_name FROM table_order INNER JOIN table_user ON table_user.id_user = table_order.id_user_order INNER JOIN table_user ON table_user.id_user = table_order.id_user_taker ");

while($data = $qry->fetch_assoc()){
echo $data['order_name'] . " " . $data['taker_name'];
}

?>





but, cannot show. Please solve my problem guys. thanks so much

Answer

When you need to join the same table two or more time you must use a proper alias for the tables

 $qry = $conn->query("select 
              table_order.*
            , a.name AS order_name
            , b.name AS taker_name 
            FROM table_order 
            INNER JOIN table_user as a ON a.id_user = table_order.id_user_order 
            INNER JOIN table_user as b ON b.id_user = table_order.id_user_taker ");