Join Tables

[Greeky]

Imagine two table One Table (ForeignCurrency) FC_ID FC_TYPE 1 USD 2 EUR 3 YTL Other table (Rates) FCV_ID FCV_RateIN FCV_RateOUT FCV_DATE 1 1 1 10/13/2005 2 1.345 1.346 10/13/2005 3 1.651 1.654 10/13/2005 I need to join two table in a one query. I do this so Select FC.FC_TYPE,ISNULL(FCV_RateIN,1) FCV_RateIN,ISNULL(FCV_RateOut,1) FCV_RateOut from ForeignCurrency FC LEFT OUTER JOIN Rates R ON FC.FC_ID=R.FCV_ID WHERE FCV_Date='10/13/2005' This give me result as i want. But when i need 10/14/2005 rates it give me empty result set , but i expect FCV_TYPE FCV_RateIn FCV_RateOUT YTL 1 1 USD 1 1 EUR 1 1 How can i re-write code ?

[Qhalis]

It looks like a misunderstanding of how the join works. The SQL Engine performs a select on the two tables using the join. The 'left outer' part ensures that if there are entries in the ForeignCurrency table that do not have corresponding entries in the Rates table, then they will still be shown. e.g Add a new entry to the ForeignCurrency table 4 IEP SELECT FC.FC_TYPE ,ISNULL(FCV_RateIN,1) FCV_RateIN ,ISNULL(FCV_RateOut,1) FCV_RateOut FROM ForeignCurrency FC LEFT OUTER JOIN Rates R ON FC.FC_ID=R.FCV_ID will give FC_TYPE FCV_RateIN FCV_RateOut USD 1 1 EUR 1.345 1.345 YTL 1.651 1.654 IEP 1 1 AFTER the join is performed the WHERE criteria are applied. Thus we check through the resultset for the FCV_Date='10/13/2005' (This is all conceptual - in practice the engine may optimize but it ACTS as if the steps happen in this order) For 10/13/2005 all the rows in your test data match, so we are good For 10/14/2005, no rows match so you get an empty set. hth Alan.