left join data removed by where clause? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

left join data removed by where clause?

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-10-16 : 11:26:49

This SQL displays everything in the InvoiceStatus table:
SELECT InvoiceStatus.[ID],
COUNT(Invoices.Invoice_No) AS 'TaskCount',
InvoiceStatus.[Description],
SUM(Invoices.Amount)
FROM InvoiceStatus
left JOIN Invoices ON Invoices.InvoiceStatus = InvoiceStatus.[ID]
WHERE InvoiceStatus.[ID] NOT IN (0, 8, 9, 10)

GROUP BY InvoiceStatus.[Description],
InvoiceStatus.[ID]
ORDER BY InvoiceStatus.[ID]

As soon as I add an extra and in the where clause, some of the InvoiceStatuses are removed. I need them to display a count of zero if there are no records for the AND in the WHERE clause:
SELECT InvoiceStatus.[ID],
COUNT(Invoices.Invoice_No) AS 'TaskCount',
InvoiceStatus.[Description],
SUM(Invoices.Amount)
FROM InvoiceStatus
left JOIN Invoices ON Invoices.InvoiceStatus = InvoiceStatus.[ID]
WHERE InvoiceStatus.[ID] NOT IN (0, 8, 9, 10)
AND Invoices.Credit_ContName = 'Joe Bloggs'
GROUP BY InvoiceStatus.[Description],
InvoiceStatus.[ID]
ORDER BY InvoiceStatus.[ID]

ANy ideas?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-16 : 11:32:12

You need to read this: http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-16 : 11:34:01

You could use a UNION , the top part of the UNION is the statement and then the bottom part of the UNION is the opposite condition

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 11:37:31

What Harsh is saying is this

SELECT		InvoiceStatus.[ID],
		COUNT(Invoices.Invoice_No) AS 'TaskCount',
		InvoiceStatus.[Description], 
		SUM(Invoices.Amount)
FROM		InvoiceStatus 
left JOIN	Invoices ON Invoices.InvoiceStatus = InvoiceStatus.[ID]
			AND Invoices.Credit_ContName = 'Joe Bloggs'
WHERE		InvoiceStatus.[ID] NOT IN (0, 8, 9, 10)
GROUP BY	InvoiceStatus.[Description], 
		InvoiceStatus.[ID]
ORDER BY	InvoiceStatus.[ID]

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-16 : 14:21:54

Peso, every now and then, you should let the OP try to find out the solution on their own after given advice on how to do it ... posting cut and paste code for them is nice and all, but they tend not to learn as much from the experience ...

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 01:49:42

Great idea!

But for this OP it doesn't work. We have helped him with similar questions for over a year now.
That is a lot of time spent.

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72253

E 12°55'05.25"
N 56°04'39.16"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-10-17 : 03:57:26

Look, I understand your reasoning but this is also the point of forums!I was quite happy to read the background to understand it but an example helps as well and now I know why it's needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources