help in select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help in select query

Author

Topic

san79
Starting Member

42 Posts

Posted - 2009-06-20 : 02:52:48

hi
i am having three tables and all of them have 3 common fields serves as primary key at any point of time any of the three tables can be with out any data when i try to join those tables to get values if there is no corresponding record which matches the primary key atleast in one table no data is returned. what i would like to know is how to display the data from the other two tables if the third is empty
this is the sample query which returns no record if any one table is empty for the particular key column.

select m.inv_no,m.inv_date,m.inv_type,m.amount,m.bal_amount,t.totdays,t.amount,
t.service_tax,
t.tds,t.pec,t.sec,t.consult_code,isnull(e.bal_amount,0)
from mst_invoice as m,trn_invoice as t,mst_expenditureinvoice as e where
(m.inv_no=t.inv_no) and (m.co_code=t.co_code) and (m.inv_no=e.inv_no) and
(m.fyear_code=t.fyear_code) and 
m.client_code='1214' and m.bal_amount>0 and m.fyear_code=103
and m.co_code=101;

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-20 : 02:57:06

[code]
select m.inv_no,m.inv_date,m.inv_type,m.amount,m.bal_amount,t.totdays,t.amount,
t.service_tax,
t.tds,t.pec,t.sec,t.consult_code,isnull(e.bal_amount,0)
from mst_invoice as m
full outer join trn_invoice as t
on m.inv_no=t.inv_no
and m.co_code=t.co_code
and m.fyear_code=t.fyear_code
and m.client_code='1214'
and m.bal_amount>0
and m.fyear_code=103
and m.co_code=101
full outer join mst_expenditureinvoice as e
on m.inv_no=e.inv_no
[/code]