[RESOLVED] php mysql join

[Thikho]

Hi

I have a form which list branches name in form of a drop down but i want to have a join of two tables which both hold branch information , i want to use a join to select Branch Name ffrom this two tables

pls help with using join in mysql

here is the code of my dropdown now which is only selecting from Branch_Information my second table is Branch_Main

<td><b>Branch:</b></td><td><select name='branch'>
<?php
include('conn.php');
$Qxo="select * from Branch_Information ORDER by Branch asc";
print "<option>Select Branch</option>";

$cat=mysql_query($Qxo) or die ("cannot select from Branch Information");
while ($list=mysql_fetch_array($cat))
{

print "<option value='$list[Branch_Name]'>$list[Branch_Name]</option>";
}
?>
</select></td>

[jeepin81]

sure thing

<?


$Qxo="select * from Branch_Information, Branch_Main ORDER by Branch asc"; 
print "<option>Select Branch</option>"; 


//or you can left join
$Qxo="select * from Branch_Information LEFT JOIN Branch_Main ON WhateverField = WhateverField ORDER by Branch asc"; 
print "<option>Select Branch</option>"; 


// or you can right join
$Qxo="select * from Branch_Information RIGHT JOIN Branch_Main ON WhateverField = WhateverField ORDER by Branch asc"; 
print "<option>Select Branch</option>"; 

?> 

A LEFT JOIN will bring all the rows from the first table and what ever rows match on the second table. a RIGHT JOIN will get all matching rows from the first table and any additional rows from the second table that don't match.

There is also an inner join, outer join, cross join, etc... I'm sure Dr. Google, MD could answer tons of join questions.

hope that helps,
j

[Thikho]

I have tried this and on my drop down it does not show anything so i THINK THERE IS SOMETHING MISSING HERE when i do the right join it does not correspond to mysql server

mysql> select Branch_Information.Branch_Name, Branch_Main.Branch_Name from Branch_Information,Branch_Main;
+------------------+---------------+
| Branch_Name | Branch_Name |
+------------------+---------------+
| Bloemfontein | JHB Main |
| Cape Town | JHB Main |
| Durban | JHB Main|
| East London | JHB Main |
| George | JHB Main|
| Johannesburg | JHB Main |
| Kimberley | JHB Main |
| Nelspruit | JHB Main |
| Port Elizabeth | JHB Main|
| Pietermaritzburg | JHB Main |
| Pretoria | JHB Main |
| Polokwane | JHB Main |
| Rustenburg | JHB Main |
| Vaal | JHB Main |
| Witbank | JHB Main |
+------------------+---------------+

I want my drop down to have the brancinfo and branchmain in one dropdwon list

mysql> select Branch_Name from Branch_Information;
+------------------+
| Branch_Name |
+------------------+
| Bloemfontein |
| Cape Town |
| Durban |
| East London |
| George |
| Johannesburg |
| Kimberley |
| Nelspruit |
| Pietermaritzburg |
| Polokwane |
| Port Elizabeth |
| Pretoria |
| Rustenburg |
| Vaal |
| Witbank |

mysql> select Branch_Name from Branch_Main;
+---------------+
| Branch_Name |
+---------------+
| JHB Main |
+---------------+
1 row in set (0.00 sec

[Thikho]

I have managed to use a union now i got my result in the right order but it does not work properly whe i upload on diff server it only works on my local host, i also need help with creating views in mysql

this is what i have done

SELECT from Branch_Information UNION select from Branch_Main

then it shows 16 row and my dropdon is listing all the branches now