Not All Rows returned in query

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)

Not All Rows returned in query

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-06-11 : 03:55:37

I have a query that looks at 2 tables, one being my data and another which is a lookup table. I want to bring all rows back from the lookup table and count the same field in my data table, but this query when runs only brings back results where the count is greater than zero, how can I bring them all back even if the result is zero?


SELECT dbo.REF_WL_RANGES.[Weeks Range Title], 
COUNT(dbo.REP_WL_DATA.[Weeks Range]) AS [Count]
FROM dbo.REF_WL_RANGES
LEFT JOIN dbo.REP_WL_DATA
ON dbo.REP_WL_DATA.[Weeks Range] = dbo.REF_WL_RANGES.[Weeks Range]
WHERE ((([Waiting List Name])='pdd') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Not Like 'phytdwr')) 
OR ((([Waiting List Name])='pdd') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Is Null)) 
OR ((([Waiting List Name])='pp') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Not Like 'phytdwr')) 
OR ((([Waiting List Name])='pp') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Is Null)) 
OR ((([Waiting List Name])='pga') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Not Like 'phytdwr')) 
OR ((([Waiting List Name])='pga') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Is Null)) 
OR ((([Waiting List Name])='pbcs') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Not Like 'phytdwr')) 
OR ((([Waiting List Name])='pbcs') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Is Null)) 
OR ((([Waiting List Name])='pbcn') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Not Like 'phytdwr')) 
OR ((([Waiting List Name])='pbcn') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Is Null)) 
OR ((([Waiting List Name])='pd') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Not Like 'phytdwr')) 
OR ((([Waiting List Name])='pd') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Is Null)) 
OR ((([Waiting List Name])='pdos') 
AND (([Date Removed from Waiting List]) Is Null) 
AND (([Waiting List Local Category]) Not Like 'phytdwr')) 
OR ((([Waiting List Name])='pdos') 
AND (([Date Removed from Waiting List]) Is Null))
AND (([Waiting List Local Category]) Is Null)
AND (dbo.REP_WL_DATA.[Month] = 'June')
AND (dbo.REP_WL_DATA.[Year] = '2009')
AND (dbo.REP_WL_DATA.[Generated Date] = '10/06/2009')
GROUP BY dbo.REF_WL_RANGES.[Weeks Range Title], dbo.REF_WL_RANGES.[Weeks Range]
ORDER BY dbo.REF_WL_RANGES.[Weeks Range]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 04:13:42

Try this rewrite. It's simpler to understand.
And you MUST tell us which table aliases to use for the code in red.

SELECT		r.[Weeks Range Title], 
		COUNT(d.[Weeks Range]) AS [Count]
FROM		dbo.REF_WL_RANGES AS r
LEFT JOIN	dbo.REP_WL_DATA AS d ON d.[Weeks Range] = r.[Weeks Range]
			AND d.[Month] = 'June'
			AND d.[Year] = '2009'
			AND d.[Generated Date] = '10/06/2009'
WHERE		{d | r}.[Waiting List Name] IN ('pdd', 'pp', 'pga', 'pbcs', 'pbcn', 'pd', 'pdos')
		AND {d | r}.[Date Removed from Waiting List] IS NULL
		AND ({d | r}.[Waiting List Local Category] IS NULL OR {d | r}.[Waiting List Local Category] <> 'phytdwr')
GROUP BY	r.[Weeks Range Title]

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-06-11 : 04:40:34

See what you mean, far simpler to read and understand :-)

Its now rewritten, but the problem is that the results are far too high. I know It should get a total of 32 for all the results. but this gives me them in the thousands.


SELECT r.[Weeks Range Title], COUNT(d.[Weeks Range]) AS [Count]
FROM		dbo.REF_WL_RANGES AS r
LEFT JOIN	dbo.REP_WL_DATA AS d 
ON d.[Weeks Range] = r.[Weeks Range]
AND d.[Month] = 'June'
AND d.[Year] = '2009'
AND d.[Generated Date] = '10/06/2009'
WHERE d.[Waiting List Name] IN ('pdd', 'pp', 'pga', 'pbcs', 'pbcn', 'pd', 'pdos')
AND d.[Date Removed from Waiting List] IS NULL
AND d.[Waiting List Local Category] IS NULL 
OR d.[Waiting List Local Category] NOT LIKE 'phytdwr'
GROUP BY r.[Weeks Range Title], r.[Weeks Range]
ORDER BY r.[Weeks Range]

I am copying the original query from Access


SELECT r.[Weeks Range Title], 
COUNT(d.[Weeks Range]) AS [Count]
FROM REF_WL_RANGES AS r
LEFT JOIN REP_WL_DATA AS d
ON d.[Weeks Range] = r.[Weeks Range]
WHERE (((d.[Waiting List Name])="pdd") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Not Like "phytdwr")) 
OR (((d.[Waiting List Name])="pdd") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Is Null)) 
OR (((d.[Waiting List Name])="pp") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Not Like "phytdwr")) 
OR (((d.[Waiting List Name])="pp") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Is Null)) 
OR (((d.[Waiting List Name])="pga") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Not Like "phytdwr")) 
OR (((d.[Waiting List Name])="pga") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Is Null)) 
OR (((d.[Waiting List Name])="pbcs") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Not Like "phytdwr")) 
OR (((d.[Waiting List Name])="pbcs") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Is Null)) 
OR (((d.[Waiting List Name])="pbcn") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Not Like "phytdwr")) 
OR (((d.[Waiting List Name])="pbcn") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Is Null)) 
OR (((d.[Waiting List Name])="pd") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Not Like "phytdwr")) 
OR (((d.[Waiting List Name])="pd") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Is Null)) 
OR (((d.[Waiting List Name])="pdos") 
AND ((d.[Date Removed from Waiting List]) Is Null) 
AND ((d.[Waiting List Local Category]) Not Like "phytdwr")) 
OR (((d.[Waiting List Name])="pdos") 
AND ((d.[Date Removed from Waiting List]) Is Null))
AND ((d.[Waiting List Local Category]) Is Null)
AND (d.[Month] = "June")
AND (d.[Year] = "2009")
AND (d.[Generated Date] = #10/06/2009#)
GROUP BY r.[Weeks Range Title], r.[Weeks Range]
ORDER BY r.[Weeks Range]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 04:50:17

It's the nature of OUTER joins. Try replace LEFT JOIN with INNER JOIN in this suggestion

SELECT		r.[Weeks Range Title], 
		COUNT(d.[Weeks Range]) AS [Count]
FROM		dbo.REF_WL_RANGES AS r
LEFT JOIN	dbo.REP_WL_DATA AS d ON d.[Weeks Range] = r.[Weeks Range]
			AND d.[Month] = 'June'
			AND d.[Year] = '2009'
			AND d.[Generated Date] = '10/06/2009'
			AND d.[Waiting List Name] IN ('pdd', 'pp', 'pga', 'pbcs', 'pbcn', 'pd', 'pdos')
			AND d.[Waiting List Local Category] <> 'phytdwr'
			AND d.[Date Removed from Waiting List] IS NULL
GROUP BY	r.[Weeks Range Title]

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-06-11 : 05:11:14

Doing this I tried the INNER JOIN & RIGHT JOIN, this gave me blanks. I then tried it again with LEFT JOIN and I got all zero's for all rows in r.[Weeks Range]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 05:17:25

Keep LEFT JOIN, and remove this line
AND d.[Date Removed from Waiting List] IS NULL

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 05:18:30

Otherwise you will have to post your table structures and same proper sample data, together with expected output.

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-06-11 : 05:31:42

I took the line out and still the same happens

This is my table structures, I send some data in a min and expected output

CREATE TABLE [dbo].[REP_WL_DATA](
[Waiting List Identifier] [int] NOT NULL,
[Patient Lorenzo Identifier] [int] NULL,
[Patient PAS Identifier] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[Patient NHS Identifier] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[Patient Gender] [varchar](80) COLLATE Latin1_General_CI_AS NULL,
[Patient Name] [varchar](62) COLLATE Latin1_General_CI_AS NULL,
[Patient DOB] [datetime] NULL,
[Referral Reference] [int] NULL,
[Referred to Clinician ID] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[Referred to Clinician Name] [varchar](62) COLLATE Latin1_General_CI_AS NULL,
[Referred to Team] [varchar](5) COLLATE Latin1_General_CI_AS NULL,
[Referred to Service] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Referral Received Date] [datetime] NULL,
[Waiting List Name] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[Waiting List Description] [varchar](80) COLLATE Latin1_General_CI_AS NULL,
[Waiting List Local Category] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[Waiting List Local Category Description] [varchar](80) COLLATE Latin1_General_CI_AS NULL,
[Date on Waiting List] [datetime] NULL,
[Date Removed from Waiting List] [datetime] NULL,
[Waiting List Team] [varchar](5) COLLATE Latin1_General_CI_AS NULL,
[Waiting List Clinician ID] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[Waiting List Clinician Name] [varchar](62) COLLATE Latin1_General_CI_AS NULL,
[Waiting List Service] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Referral Archive Flag] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
[Patient Archive Flag] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
[Create User] [varchar](35) COLLATE Latin1_General_CI_AS NULL,
[WL_TYPE] [varchar](3) COLLATE Latin1_General_CI_AS NULL,
[Speciality] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
[Care Group Code] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
[Care Group] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
[Weeks Waiting] [int] NULL,
[Weeks Range] [int] NULL,
[Weeks Range Title] [varchar](5) COLLATE Latin1_General_CI_AS NULL,
[Month] [varchar](15) COLLATE Latin1_General_CI_AS NULL,
[Year] [varchar](5) COLLATE Latin1_General_CI_AS NULL,
[Generated Date] [datetime] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[REF_WL_RANGES](
[Weeks Range Title] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[Weeks Range] [int] NOT NULL
) ON [PRIMARY]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 05:35:14

The question is "Do you want to get all records from Ranges table, and only those matching from Data table?".
Or only the Ranges where there are matching records in Data table?

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-06-11 : 05:35:53

REF_WL_RANGES

0<4 1
4<8 2
8<13 3
13<17 4
17<21 5
21+ 6

REP_WL_Data

10016748 12285984 4705869672 4705869672 Male Mr Patient 29/05/1937 00:00 10027008 E35 Lisle,Jez DTM DIET 03/11/2003 00:01 ERD5 Diet CONT May Diabetic Review NULL NULL 03/11/2003 00:00 NULL NULL E29 Lisle,Jez DIET N N NULL NSP Physio SPC Scheduled & Primary Care 291 6 21+ June 2009 10/06/2009 00:00
10016781 12200935 4540439286 4540439286 Female Miss Patient 06/06/1951 00:00 10025395 E35 Lisle,Jez DTM DIET 23/09/2003 00:01 ERD6 Diet CONT Jun Diabetic Review NULL NULL 23/09/2003 00:00 NULL NULL E29 Lisle,Jez DIET N N NULL NSP Physio SPC Scheduled & Primary Care 296 6 21+ June 2009 10/06/2009 00:00
10016056 12295642 4708168462 4708168462 Female Mrs Patient 29/03/1933 00:00 10028771 E35 Lisle,Jez DTM DIET 27/11/2000 00:01 ERD3 Diet CONT Mar Diabetic Review NULL NULL 27/11/2000 00:00 NULL NULL E29 Lisle,Jez DIET N N NULL NSP Physio SPC Scheduled & Primary Care 444 6 21+ June 2009 10/06/2009 00:00
10020243 12286552 4705889959 4705889959 Female Ms Patient 30/03/1927 00:00 10035396 E35 Lisle,Jez DTM DIET 02/12/2002 00:01 ERD3 Diet CONT Mar Diabetic Review NULL NULL 02/12/2002 00:00 NULL NULL E29 Lisle,Jez DIET N N NULL NSP Physio SPC Scheduled & Primary Care 339 6 21+ June 2009 10/06/2009 00:00
10020054 12287356 4705926226 4705926226 Male Mr Patient 25/08/1927 00:00 10029037 E35 Lisle,Jez DTM DIET 29/09/2000 00:01 ERD8 Diet CONT Aug Diabetic Review NULL NULL 29/09/2000 00:00 NULL NULL E29 Lisle,Jez DIET N N NULL NSP Physio SPC Scheduled & Primary Care 452 6 21+ June 2009 10/06/2009 00:00
10020595 12493435 6168710574 6168710574 Female Mrs Patient 16/12/1975 00:00 10029078 E35 Lisle,Jez DTM DIET 11/12/2000 00:01 ERD12 Diet CONT Dec Diabetic Review NULL NULL 11/12/2000 00:00 NULL NULL E29 Lisle,Jez DIET N N NULL NSP Physio SPC Scheduled & Primary Care 442 6 21+ June 2009 10/06/2009 00:00

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-06-11 : 05:36:24

The Results are supposed to look like this

0<4 28
4<8 4
8<13 0
13<17 0
17<21 0
21+ 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 05:39:08

Start with the smallest common acceptable filter:

SELECT		r.[Weeks Range Title], 
		COUNT(d.[Weeks Range]) AS [Count]
FROM		dbo.REF_WL_RANGES AS r
LEFT JOIN	dbo.REP_WL_DATA AS d ON d.[Weeks Range] = r.[Weeks Range]
			AND d.[Month] = 'June'
			AND d.[Year] = '2009'
GROUP BY	r.[Weeks Range Title]

Then add more and more criterias in the LEFT JOIN part.

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-06-11 : 06:22:13

I tried the build up line by line, and have now got all the rows from REF_WL_Ranges but the count is Zero

instead of using AND d.[Waiting List Name] IN ('pdd', 'pp', 'pga', 'pbcs', 'pbcn', 'pd', 'pdos') I did them as individual lines to test what that does,

SELECT r.[Weeks Range Title],
COUNT(d.[Weeks Range]) AS [Count]
FROM dbo.REF_WL_RANGES AS r
LEFT JOIN dbo.REP_WL_DATA AS d ON d.[Weeks Range] = r.[Weeks Range]
AND d.[Month] = 'June'
AND d.[Year] = '2009'
AND d.[Generated Date] = '10/06/2009'
AND d.[Waiting List Local Category] NOT LIKE 'phytdwr'
AND d.[Waiting List Name] = 'pdd'
AND d.[Waiting List Name] = 'pp'
AND d.[Waiting List Name] = 'pga'
AND d.[Waiting List Name] = 'pbcs'
AND d.[Waiting List Name] = 'pbcn'
AND d.[Waiting List Name] = 'pd'
AND d.[Waiting List Name] = 'pdos'
GROUP BY r.[Weeks Range Title]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 06:35:52

A column cannot have two different values at the same time.
Do you get a count other than zero for this query?

SELECT		r.[Weeks Range Title], 
		COUNT(d.[Weeks Range]) AS [Count]
FROM		dbo.REF_WL_RANGES AS r
LEFT JOIN	dbo.REP_WL_DATA AS d ON d.[Weeks Range] = r.[Weeks Range]
			AND d.[Month] = 'June'
			AND d.[Year] = '2009'
			AND d.[Generated Date] = '10/06/2009' -- Is this June 10 or October 6? Use ISO format if possible; 2009MMDD.
GROUP BY	r.[Weeks Range Title]

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-06-11 : 07:14:19

The Generated Date is 10th June

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 07:16:48

[code]SELECT r.[Weeks Range Title],
COUNT(d.[Weeks Range]) AS [Count]
FROM dbo.REF_WL_RANGES AS r
LEFT JOIN dbo.REP_WL_DATA AS d ON d.[Weeks Range] = r.[Weeks Range]
AND d.[Month] = 'June'
AND d.[Year] = '2009'
AND d.[Generated Date] = '20090610'
GROUP BY r.[Weeks Range Title][/code]What does this suggestion return?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources