(id) mother son
(1) Europe France
(2) Asia1 China
(3) Japan Senkaku
(4) Newyork Manhatan
(5) Asia2 Japan
(6) Africa Egypt
(7) France Paris
(8) Asia1 Russia
(9)China Senkaku
(10) Japan Tokyo
(11)Europe Russia
I have a table named geo5 like the above.
I have two keyword, i.e, Asia1 and Senkaku.
I like to find China which is son of ‘Asia1’. and mother of ‘Senkaku’.
I can find it by the code below.
$list=mysql_query("select mother from geo5
22 WHERE son='senkaku'
23 order by id");
24
25 while($rows=mysql_fetch_array($list)){
26 $mother=$rows['mother'];
27
28 $query="SELECT mother as grand_mother FROM geo5
29 WHERE son='$mother'";
30
31 $sql=mysql_query($query); $row=mysql_fetch_assoc($sql);
32
33 $grand_mother=$row['grand_mother'];
34 if($grand_mother=='Asia1'){echo $mother;}
35 }The result of code above is ‘China’ which is my target result.
So far so good.
However, I think if I use subquery I don’t need the code above.
The following is one of my trial for the subquery but I failed,
$query="SELECT son as target
38 FROM (SELECT mother FROM geo5
39 WHERE son='Senkaku') T
40 WHERE mother='Asia1'" ;
41
42 $sql=mysql_query($query); $row=mysql_fetch_assoc($sql); $target= $row['target'];
As I am not good at subquery, I hope you suggest correct subquery for it.