Join Not Working - 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

General SQL Server Forums

New to SQL Server Programming

Join Not Working

Author

Topic

vineet.tanwar
Starting Member

5 Posts

Posted - 2012-04-27 : 03:03:31

Hi, I Have two tables

First CustomerBillNo

CBillID
CPOID
BillDate

2. CustomerBillItem

CBIllID
ItemID
QtyA
QtyB
PriceA
PriceB

Now i using this query

SELECT isnull(SUM((ISNULL(dbo.CustomerBillItem.qtya,0)* ISNULL(dbo.customerbillitem.pricea,0)) + (ISNULL(dbo.CustomerBillitem.qtyb,0) * ISNULL(dbo.customerbillitem.priceb,0))),0)
FROM dbo.CustomerBillItem LEFT JOIN
dbo.CustomerBillNo ON dbo.CustomerBillItem.CBillID = dbo.CustomerBillNo.CBillID
where (dbo.CustomerBillNo.BillDate between '2012-04-26' and '2012-04-27') and dbo.CustomerBillitem.ItemID = 'FR4523-544'
and dbo.CustomerBillItem.Status = '1' and dbo.CustomerBillNo.Status = '1'

while the item exists in customerbillitem but query returned me 0.00

Please Help Thank You

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-27 : 03:33:26

Please post some sample data.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-27 : 05:02:35

You are using a LEFT JOIN and you also have where-criteria that filters in the right table. Try this and see what you get:


SELECT isnull(SUM((ISNULL(dbo.CustomerBillItem.qtya,0)* ISNULL(dbo.customerbillitem.pricea,0)) + (ISNULL(dbo.CustomerBillitem.qtyb,0) * ISNULL(dbo.customerbillitem.priceb,0))),0)
FROM dbo.CustomerBillItem 
  LEFT JOIN dbo.CustomerBillNo 
    ON dbo.CustomerBillItem.CBillID = dbo.CustomerBillNo.CBillID
      and (dbo.CustomerBillNo.BillDate between '2012-04-26' and '2012-04-27') 
      and dbo.CustomerBillitem.ItemID = 'FR4523-544'
      and dbo.CustomerBillItem.Status = '1' 
      and dbo.CustomerBillNo.Status = '1'

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 15:28:51

understand the reason here

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources