Mysql – Fetch data from database from multiple tables matching condition

database-designMySQL

I want to fetch data from the database. I have three tables in my database like listing_master_residential and listing_master_condo and listing_master_commercial. There are three tables and there is one primary key Ml_num in all tables. I want to search the data form one table which matches mls number table.

if(isset($_POST['search'])){
$mls=$_POST['mls_number'];
 $sql = "SELECT * FROM listing_master_residential
 , listing_master_condo 
, listing_master_commercial  
 INNER JOIN listing_master_residential AS res ON res.Ml_num=Ml_num
 INNER JOIN listing_master_condo AS con ON con.Ml_num=Ml_num
INNER JOIN listing_master_commercial AS com ON com.Ml_num=Ml_num
 WHERE Ml_num='$mls'";
    $result = $wpdb->get_results($sql) or die(mysql_error());
foreach( $result as $row){
echo $row->Lot_code."<br/>";
echo $row->Ml_num."<br/>";
echo $row->Acres;
echo $row->Addr."<br/>";
echo $row->Bath_tot;
echo $row->Br;
echo $row->Br_plus;
}

 }

 }

With the above, I get an error

Column 'Ml_num' in where clause is ambiguous

Best Answer

SELECT
    *
FROM
    listing_master_residential res  
    INNER JOIN listing_master_condo con ON res.Ml_num = con.Ml_num
    INNER JOIN listing_master_commercial com ON com.Ml_num = res.Ml_num
WHERE
    res.Ml_num = '$mls'

Also, instead of selecting ALL (*), select only the columns you will be using, this will help your query perform better.

Related Question