从第2和第3个表中获取第1个表中匹配值的值

Question

I am a jQuery developer and I do not have good idea on how to retrieve data from tables. I am just trying to learn it.

I have a special case to deal with. Consider three tables

tb1 
-----------------------------------------------
    pid    fname     lname       cid       eid
    12      jo         mo        16345     2345
    13      ko         ro        16324     2435

tb2
-----------------------------------------------
    cid     cname
   16345    amazed
   16324    bored

tb3
------------------------------------------------
    eid      ename
    2345      nolo
    21345     johny

I want to retrieve the data for the matching pid value. Say,

select * from tb1 where pid = 12 

It returns me pid, fname, lname, cid, eid

The cid value is 16345, Instead the number. I want that cid value to be matched to the tb2 and get the cname column value that is amazed and same with the eid that is the ename : nolo I want those numbers to be matched to tb2 and tb3 and get their string values.

I could do it using 3 MySQL queries

This is what I have tried

 $result = mysql_query("select * from tb1 where pid = 12");
 $row = mysql_fetch_array($result,MYSQL_ASSOC);
 $cname = mysql_query("select cname from tb2 where cid = '$row[cid]'");
 $x =  mysql_fetch_array($cname,MYSQL_ASSOC);
 $cname = mysql_query("select ename from tb3 where eid = '$row[eid]'"); 
 $y =  mysql_fetch_array($cname,MYSQL_ASSOC);

But I feel the code is not ideal. I guess there must be a way to retrieve in a single query but I am not sure what it must be. Maybe joins or sub-queries.

Answer 1

Use joins on 3 tables

SELECT tb1.*, tb2.cname, tb3.ename from tb1
JOIN tb2 ON tb1.cid = tb2.cid
JOIN tb3 ON tb1.eid = tb3.eid
WHERE tb1.pid = 12

Hope this helps