Hi,
I have 15 tables in my database and all tables has common column called "CustomerName".
I want to retrive the data from 15 tables where customerName ='XYZ'.
I tried this one:
select *
from Table1, Table2.....Table15
where CustomerName='XYZ';
Darma
Loading
Manish TewatiaPosted Dec 10, 2012, 5:27 AM
You can use the join for this....
select * from
Table1 t1 join Table2 t2 on t1.name = t2.name join
Table3 t3 on t1.name = t3.name join Table4 t4 on t1.name = t4.name join
Table5 t5 on t1.name = t5.name join Table6 t6 on t1.name = t6.name join
Table7 t7 on t1.name = t7.name join Table8 t8 on t1.name = t8.name join
Table9 t9 on t1.name = t9.name join Table10 t10 on t1.name = t10.name join
Table11 t11 on t1.name = t11.name join Table12 t12 on t1.name = t12.name join
Table13 t13 on t1.name = t13.name join Table14 t14 on t1.name = t14.name join
Table15 t15 on t1.name = t15.name
Thanks
Vithal WadjePosted Dec 10, 2012, 5:12 AM
when you retrive multiple records from different tables with same coloumn name then sql becomes ambigutes (does not reconize) so to avoid this problem you need to give alies name to each table to display records so use following query
Blocked AccountPosted Dec 10, 2012, 5:06 AM
If you write query like above, then it will apply cross join, you will not get accurate result.
if you want to fetch records from multiple table have some common column, then you have to write like below. In this i am considering two tables. You can increase accordingly.
select *
from Table1, Table2
where Table1.CustomerName='XYZ'
and Table1.UserName = Table2.UserName