Fetch data from database from multiple tables one primary key

Question

I want to fetch data from the database. I have three tables in my database like: listing_master_residential, 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.

$sql ="SELECT * FROM listing_master_residential ,listing_master_commercial, listing_master_condo WHERE Ml_num=$option";

i want to show only those data to match ml_num in above table

possible duplicate of [SQL JOIN and different types of JOINs](http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — rack_nilesh, Dec 17 '14 at 10:49
here i am using cross join but showing an error Column 'Ml_num' in where clause is ambiguous — avktech, Dec 17 '14 at 10:55

Debug Diva · Answer 1 · 2014-12-17T11:53:37.000

0

Try This by using inner join:

SELECT t1.Ml_num,t2.*,t3.Ml_num FROM listing_master_residential t1 
JOIN listing_master_commercial t2 ON t1.Ml_num = 'W2726470' 
JOIN listing_master_condo t3 ON t1.Ml_num = 'W2726470'

I hope it will help you.

edited Dec 17 '14 at 11:53

answered Dec 17 '14 at 10:53

Debug Diva

26,058
13
70
123

i want fetch all column where match my condition in above one table as a example my ml_num match with listing_master_commercial as all the field . – avktech Dec 17 '14 at 10:58
@avktech, so no issue you can fetch all the fields. I edted my answer please check. – Debug Diva Dec 17 '14 at 11:01
$sql ="SELECT t1.Ml_num,t2.*,t3.Ml_num FROM listing_master_residential t1 JOIN listing_master_commercial t2 ON t1.Ml_num = $option JOIN listing_master_condo t3 ON t1.Ml_num = $option"; – avktech Dec 17 '14 at 11:08
showing a error Unknown column 'W2726470' in 'on clause' – avktech Dec 17 '14 at 11:08
@avktech, You doing in a wrong way. what is `$option` here ? – Debug Diva Dec 17 '14 at 11:18
option here condition ml_num=W2726470 which table ml_num show data – avktech Dec 17 '14 at 11:24

Fetch data from database from multiple tables one primary key

1 Answers1