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

SQL Server Development (2000)

Subquery

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-12-09 : 11:59:39

Guys,

I have following query that is erroring out, I know the problem but cannot find ways to fix it

SELECT mTL.TYPE AS TYPE,
(SELECT COUNT(TYPE) FROM dbo.LINE s1TL INNER JOIN
dbo.HEADER s1TH ON s1TL.ID = s1TH.ID
WHERE s1TL.Type = 1 and
(s1TH.RECDATE BETWEEN CONVERT(DATETIME, '12/01/2005') AND CONVERT(DATETIME, '12/08/2005 23:59:59'))
AND (s1TL.TYPE = mTL.TYPE

--- and ID = mTL.ID

) GROUP BY s1TL.TYPE) as DocCount
FROM dbo.HEADER mTH INNER JOIN
dbo.LINE mTL ON mTH.ID = mTL.ID
WHERE mTL.type = 1 and
(mTH.RECDATE BETWEEN CONVERT(DATETIME, '12/01/2005') AND CONVERT(DATETIME, '12/08/2005 23:59:59'))
GROUP BY TYPE

When I run the query including 'and ID = mTL.ID' it gives me an error
'column 'mTL.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

The purpose of including 'and ID = mTL.ID' Clause is to select only those rows which are part of main query and that way I can avoid date selection in sub query.

Is there any I can accomplish this with out getting errors

Any suggestions/inputs would help

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-09 : 13:17:34

I think you're better off including the date selection in your subquery and omitting the "and id = mtl.id". That criteria turns your subquery into a correlated subquery. Meaning your subquery will execute once for each row in the outer query.

Doesn't this give you the same results?


SELECT	mTL.TYPE AS TYPE
	,COUNT(mTL.TYPE) as DocCount 
FROM	dbo.HEADER mTH 
INNER JOIN dbo.LINE mTL 
		ON mTH.ID = mTL.ID 
WHERE	mTL.type = 1 
and 	(mTH.RECDATE BETWEEN CONVERT(DATETIME, '12/01/2005') 
			AND CONVERT(DATETIME, '12/08/2005 23:59:59'))
GROUP BY mTL.TYPE

Be One with the Optimizer
TG

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-12-09 : 13:18:20

I must be missing something, or you haven't pasted the actual query because I see several problems:

1. (s1TH.RECDATE BETWEEN CONVERT(DATETIME, '12/01/2005') AND CONVERT(DATETIME, '12/08/2005 23:59:59'))
The table you have names is s1TL, so I don't see how this will work.

2. I don't see a join to the mTL table at all inside your sub-select so I can't see anyway for it to possibly know how to make that connection.

3. You also compare s1TL.TYPE = mTL.TYPE and again I don't see a join to that table in your sub-select.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-12-09 : 13:25:22

Never mind on all 3, I was missing something.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources