Bad Query: There has to be a better way

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

Transact-SQL (2008)

Bad Query: There has to be a better way

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-11-15 : 16:17:25

[code]
SELECT students.studentsid AS userid,
MAX(firstname) AS
firstname,
MAX(lastname) AS
lastname,
MAX(email) AS email,
(SELECT departmentname
FROM absorb.dbo.departments departments
WHERE departments.departmentid = MAX(students.departmentid)) AS
departmentname,
COUNT(DISTINCT weightentries.entryid) AS
totalentries,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries we
WHERE we.userid = weightentries.userid
AND we.weeknum = 1) AS week1,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries we
WHERE we.userid = weightentries.userid
AND we.weeknum = 2) AS week2,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries we
WHERE we.userid = weightentries.userid
AND we.weeknum = 3) AS week3,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries we
WHERE we.userid = weightentries.userid
AND we.weeknum = 4) AS week4,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries we
WHERE we.userid = weightentries.userid
AND we.weeknum = 5) AS week5,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries we
WHERE we.userid = weightentries.userid
AND we.weeknum = 6) AS week6,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries we
WHERE we.userid = weightentries.userid
AND we.weeknum = 7) AS week7,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries we
WHERE we.userid = weightentries.userid
AND we.weeknum = 8) AS week8,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries we
WHERE we.userid = weightentries.userid
AND we.weeknum = 9) AS week9,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries we
WHERE we.userid = weightentries.userid
AND we.weeknum = 10) AS week10,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries firstweek
WHERE firstweek.userid = weightentries.userid
AND firstweek.weeknum = MIN(weightentries.weeknum)) AS
startweight,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries lastweek
WHERE lastweek.userid = weightentries.userid
AND lastweek.weeknum = MAX(weightentries.weeknum)) AS
currentweight,
(SELECT TOP 1 weight
FROM weightloss.dbo.weightentries firstweek
WHERE firstweek.userid = weightentries.userid
AND firstweek.weeknum = MIN(weightentries.weeknum)) -
(SELECT TOP 1 weight
FROM
weightloss.dbo.weightentries lastweek
WHERE
lastweek.userid = weightentries.userid
AND lastweek.weeknum = MAX(weightentries.weeknum)) AS
weightlost,
MIN(weightentries.dateadded) AS
firstentry,
MAX(weightentries.dateadded) AS
currententry,
'View...' AS [View],
students.extra1,
students.extra2,
students.extra3,
students.extra4,
students.extra5,
students.extra6,
students.extra7,
students.extra8,
students.extra9,
students.extra10,
students.extra11,
students.extra12,
students.extra13,
students.extra14,
students.extra15,
students.extra16,
students.extra17,
students.extra18,
students.extra19,
students.extra20,
students.extra21,
students.extra22,
students.extra23,
students.extra24,
students.extra25,
students.extra26,
students.extra27,
students.extra28,
students.extra29,
students.extra30
FROM students
INNER JOIN subscription
ON subscription.studentsid = students.studentsid
LEFT JOIN weightloss.dbo.weightentries weightentries
ON students.studentsid = weightentries.userid
WHERE students.clientid = 0166
AND students.activestatus IN ( 1 )
GROUP BY students.studentsid,
weightentries.userid,
students.firstname,
students.lastname,
students.extra1,
students.extra2,
students.extra3,
students.extra4,
students.extra5,
students.extra6,
students.extra7,
students.extra8,
students.extra9,
students.extra10,
students.extra11,
students.extra12,
students.extra13,
students.extra14,
students.extra15,
students.extra16,
students.extra17,
students.extra18,
students.extra19,
students.extra20,
students.extra21,
students.extra22,
students.extra23,
students.extra24,
students.extra25,
students.extra26,
students.extra27,
students.extra28,
students.extra29,
students.extra30
ORDER BY lastname ASC;
[/code]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-11-15 : 16:59:24

You are right, there is a much better way, use a pivot query, search the forum or google for examples... There are many available.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 00:56:27

why so much fields in GROUP By? what are these extra fields?

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

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-11-16 : 11:31:27

quote:
Originally posted by visakh16

why so much fields in GROUP By? what are these extra fields?

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

I have to group by every field that isn't aggregated. The fields store "extra" students data that may or may not be used by clients. It is a design flaw for sure. They should be in a different table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 11:37:11

quote:
Originally posted by ferrethouse

quote:
Originally posted by visakh16

why so much fields in GROUP By? what are these extra fields?

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

I have to group by every field that isn't aggregated. The fields store "extra" students data that may or may not be used by clients. It is a design flaw for sure. They should be in a different table.

why not do grouping in a derived table first and then join it to rest of the part instead adding so much columns in group by.

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

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-11-17 : 17:06:15

quote:
why not do grouping in a derived table first and then join it to rest of the part instead adding so much columns in group by.

Primarily because I don't know how to do that ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources