SQL Server 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

Development Tools

ASP.NET

SQL Server Subquery

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-06 : 07:59:19

LB writes "We are using ASP.Net and VB.Net and SQL Server.

I have two SQL Server queries. When run separately I get the correct result. When combined as a subquery, my results are wrong. I am not asking for a solution...but to point me to the right direction. Thank you in advance and thank you for your time. Regards LB

1st SQL query, where SQLStmtFromButton is LUT CS,
eTS_TimeSheetYear = 2006
eTS_TimeSheetCycle = 22

CommandText = "SELECT T1.EmpBatchID, Count(T1.EmpBatchID) as EmpTSTotal " _
& "FROM tblTimeSheets T1 " _
& "WHERE T1.EmpBatchID = '" & SQLStmtFromButton & "' " _
& "AND T1.HoursCode = '90' " _
& "AND T1.CurrentYear = '" & eTS_TimeSheetYear & "' " _
& "AND T1.CurrentCycle = '" & eTS_TimeSheetCycle & "' " _
& "GROUP BY T1.EmpBatchID " _
& "ORDER BY T1.EmpBatchID "
---------------------------------------------------------

2nd SQL query, where SQLStmtFromButton is LUT CS,
eTS_TimeSheetYear = 2006
eTS_TimeSheetCycle = 22

CommandText = "SELECT T1.EmpBatchID, Count(T1.EmpBatchID) as EmpTSTotal " _
& "FROM tblTimeSheets T1 " _
& "WHERE T1.EmpBatchID = '" & SQLStmtFromButton & "' " _
& "AND T1.HoursCode = '90' " _
& "AND T1.CurrentYear = '" & eTS_TimeSheetYear & "' " _
& "AND T1.CurrentCycle = '" & eTS_TimeSheetCycle & "' " _
& "GROUP BY T1.EmpBatchID " _
& "ORDER BY T1.EmpBatchID "

--------------------------------------------------

Combined SQL query # 1 and 2, where SQLStmtFromButton is LUT CS,
eTS_TimeSheetYear = 2006
eTS_TimeSheetCycle = 22

CommandText = "SELECT T1.EmpBatchID, Count(T1.EmpBatchID) as EmpTSTotal, " _
& " T2.EmpBatchID as T2EmpBatchID, Count(T2.EmpBatchID) as MgrTSTotal " _
& "FROM tblTimeSheets T1 INNER JOIN tblTimeSheets T2 ON " _
& " (T2.EmpBatchID = T1.EmpBatchID " _
& " and T2.HoursCode = '91' " _
& " and T2.CurrentYear = T1.CurrentYear " _
& " and T2.CurrentCycle = T1.CurrentCycle ) " _
& "WHERE T1.EmpBatchID = '" & SQLStmtFromButton & "' " _
& "AND T1.HoursCode = '90' " _
& "AND T1.CurrentYear = '" & eTS_TimeSheetYear & "' " _
& "AND T1.CurrentCycle = '" & eTS_TimeSheetCycle & "' " _
& "GROUP BY T1.EmpBatchID, T2.EmpBatchID " _
& "ORDER BY T1.EmpBatchID, T2.EmpBatchID "
-----------------------------------"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 08:50:02

Why is the results wrong? Too many records? Too few?

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-06 : 12:06:08

The first two queries are identical, so not surprising that they give the correct results?

You're not using a subquery, you're using a join and you're including things (like T2.HoursCode = '91') in the join that almost certainly should be there.

You're also including T1.EmpBatchID and T2.EmpBatchID in the GROUP BY, but they are in the join, so you only have to GROUP BY one of them, T1.EmpBatchID.

So you have a number of things that you'll need to work on, but to help you further you need to give some example data and also an example of what the final output should be.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 12:09:59

quote:
Originally posted by snSQL

You're also including T1.EmpBatchID and T2.EmpBatchID in the GROUP BY, but they are in the join, so you only have to GROUP BY one of them, T1.EmpBatchID.

This is not necessary true.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-06 : 12:44:01

quote:
Originally posted by Peso

quote:
Originally posted by snSQL

You're also including T1.EmpBatchID and T2.EmpBatchID in the GROUP BY, but they are in the join, so you only have to GROUP BY one of them, T1.EmpBatchID.
This is not necessary true.

Peter Larsson
Helsingborg, Sweden

Given that it's an INNER JOIN, why wouldn't you just remove T2.EmpBatchID from the SELECT list and the GROUP BY? Of course we don't really know what LB wants to do, so maybe it should be an OUTER JOIN, in which case T2.EmpBatchID may need to be in the GROUP BY but I was referring to how the code is currently written.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 13:24:12

My bad! You're right.
I apologize. I misread the query...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources