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.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments