> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1143495213.186157.100040@.u72g2000cwu.googlegroups.com...
>
> Joe, you've fallen into the same trap that ALL the other posters fell into
.
> The original question was...
> Find all the students who got a grade 85 or better in math, physics and
> chemistry.
> That is you need all three rows for a single student. And only of all thr
ee
> rows are over 85 select the student. Hence why I did the solution which
> uses a pivot.
> Colin.
Colin,
Have you actually *tried* most of the other solutions posted here? I
think you'd be surprised. Okay, retlaws solution was wrong. Samis
solution works (it aliases the StudentGrades table three times in it's
from clause, then applies the appropriate criteria to each one). Yours
works. Erlands works (his finds ANY students who *do not* have a grade
less than or equal to 85 - though admittedly he has implicitly assumed
that the students have grades in all three subjects, and only in those
three subjects). Joe Celkos works (using, as he stated, relational
division). David's also works, though amazingly it seems to be the only
solution by anyone else that you haven't criticized.
There are many ways to skin a cat. For the OP, if this is a regularly
run thing, rather than a one off, take all of the solutions, try them
out, and see which perform best for your data.
Damien"Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
news:1143531396.557955.277170@.t31g2000cwb.googlegroups.com...
> Colin Dawson wrote:
> Colin,
> Have you actually *tried* most of the other solutions posted here? I
> think you'd be surprised. Okay, retlaws solution was wrong. Samis
> solution works (it aliases the StudentGrades table three times in it's
> from clause, then applies the appropriate criteria to each one). Yours
> works. Erlands works (his finds ANY students who *do not* have a grade
> less than or equal to 85 - though admittedly he has implicitly assumed
> that the students have grades in all three subjects, and only in those
> three subjects). Joe Celkos works (using, as he stated, relational
> division). David's also works, though amazingly it seems to be the only
> solution by anyone else that you haven't criticized.
> There are many ways to skin a cat. For the OP, if this is a regularly
> run thing, rather than a one off, take all of the solutions, try them
> out, and see which perform best for your data.
> Damien
>
I did try the ones which had been posted prior to my answer. I didn't
believe they would work and wanted to confirm it.
hmmm, I think as you're saying that some of them do work, I need to check
some of these out again. Maybe I've missed something that you've noticed.
Regards
Colin Dawson.|||Thank you all for the inputs ... very informative. I've already implemented
the pivot
solution in my development environment and it is working as expected. In my
attempt to
simplify my problem as much as possible, I may have used a too simple hypoth
etical
situation.
Since some like to see DDL, here is the table I'm using;
CREATE TABLE ComputerAttribute (
ComputerID int NOT NULL , -- FK into the Computer table
isTarget bit NOT NULL , -- Target/source computer a
ttribute.
AttributeID smallint NOT NULL , -- ID as defined in IDLookup tab
le
StringValue varchar (256) NULL , -- Attribute defined as a string
NumericValue int NULL -- Attribute defined as num
eric
)
Here is a about 1/3 of the possible values in AttributeID :
1 NetBIOSName
2 MACAddress
3 AssetTag
4 SerialNumber
5 PrimaryUser
6 Owner
7 Model
8 Vendor
9 NetBIOSDomain
10 DNSDomain
11 ComputerPUID
12 UUID
13 IPAddress
14 Country
15 State
15 Province
16 City
17 Street
18 Building
19 Floor
20 Department
21 OSName
22 Role
23 CPUType
24 MemoryMB
Below is a sample NON SYNTAXICALLY VALID SQL statement that reflect what I w
as s
ing inthe first place:
SELECT * FROM ComputerAttribute WHERE ComputerID IN (SELECT ComputerID WHERE
(AttributeID=MACAddress AND StringValue='00:11:22:33:44:55') AND (AttributeI
D=NetBIOSName
AND StringValue like 'usca%') AND (AttributeID=MemoryMB AND NumericValue>25
6) FROM
ComputerAttribute)
Of course the above statement will never work. As indicated, I implemented
in C# code a
pivot like solution as described in the post replies. Here is a valid SQL st
atement that
the code currently generates based frim the query selections made on a WEB p
age;
SELECT CA.ComputerID,
Q1=ISNULL((SELECT TOP 1 1 FROM ComputerAttribute WHERE ComputerID=CA.Compute
rID AND
isTarget IN (0,1) AND AttributeID=1 AND StringValue LIKE 'usca%'),NULL),
Q2=ISNULL((SELECT TOP 1 1 FROM ComputerAttribute WHERE ComputerID=CA.Compute
rID AND
isTarget IN (0,1) AND AttributeID=27 AND NumericValue > 256),NULL)
INTO #ADSFSTEMP FROM ComputerAttribute CA GROUP BY CA.ComputerID
Once I have the ComputerID values in the #ADSFSTEMP table, I use its content
to retrieve
data from other tables. I use only the ComputerID values in #ADSFSTEMP for
which all the
Q* columns are not null; "SELECT ComputerID FROM #ADSFSTEMP WHERE Q1 IS NOT
NULL AND Q2 IS
NOT NULL"
Again, thank you all for the inputs ... very informative indeed.
Gaetan.|||First of all, I'd like to make an apology. I said that most of these
answers don't work, but they do!
Some of the procedures needed tweaking so that they ran, but the solutions
do work.
Sami's, David Portas, Erland Sommarskog all return the same and correct
result.
Further to this, I figured that it would be worthwhile performing a little
further analysis to find out which is the best solution. i.e. the most
efficient.
First I ran the four solutions as a single batch, with the show actual
execution plan turned on. I'll post the actual ddl and dml at the bottom of
this post, so that you can all confirm these results.
There's the % of the batch taken by each solution
Colin's 16%
Sami's 49%
David Portas 16%
Erland Sommarskog 20%
Ok, let's take a closer look at Sami's solution. In effect, using the table
alias's it needs to perform 3 passes on the data, this will take time. Each
distinct pass is looking for a specific item, then finally joins the three
tables together to produce the result.
Erland solution he's described well himself.
Which leaves mine and David's. Running these two items in isolation they're
not identical, but they both perform only one pass on the data. The two
execution plans actually perform the same actions, but in a different order.
The real difference is the compute scalar node on the plan. In my solution
this is performing the case statements. In david's it's performing an
implicit convert to an int. (I think on the results count).
There really is nothing to choose between the two execution plans.
What about other statistics?
well set statics io on, shows that the queries are identical.
SQL Profiler, shows exactly the same.
Basically, there's absolutly nothing to choose between these two perfectly
valid results.
Regards
Colin Dawson
www.cjdawson.com
p.s. Here's the DDL
create database studenttest
go
use studenttest
Go
create table students(
student varchar(50),
subject varchar(50),
grade integer
)
Go
Insert Into students( student, subject, grade ) values ( 'student1', 'math',
80 )
Insert Into students( student, subject, grade ) values ( 'student2', 'math',
85 )
Insert Into students( student, subject, grade ) values ( 'student3', 'math',
70 )
Insert Into students( student, subject, grade ) values ( 'student1',
'physics', 90 )
Insert Into students( student, subject, grade ) values ( 'student2',
'physics', 80 )
Insert Into students( student, subject, grade ) values ( 'student3',
'physics', 75 )
Insert Into students( student, subject, grade ) values ( 'student1',
'chemistry', 80 )
Insert Into students( student, subject, grade ) values ( 'student2',
'chemistry', 90 )
Insert Into students( student, subject, grade ) values ( 'student3',
'chemistry', 95 )
Insert Into students( student, subject, grade ) values ( 'student4', 'math',
86 )
Insert Into students( student, subject, grade ) values ( 'student4',
'physics', 86 )
Insert Into students( student, subject, grade ) values ( 'student4',
'chemistry', 86 )
--Here's a student that didn't take physics, just to see if I can upset the
apple cart
Insert Into students( student, subject, grade ) values ( 'student5', 'math',
86 )
Insert Into students( student, subject, grade ) values ( 'student5',
'chemistry', 86 )
Go
--Colin's Solution
Select
s.student
From (
Select
student,
max( case when subject = 'math' then grade end ) math,
max( case when subject = 'physics' then grade end ) physics,
max( case when subject = 'chemistry' then grade end ) chemistry
From students
Group by student
) s
where s.math > 85
and s.physics > 85
and s.chemistry > 85
--Sami's solution
Select SGMaths.student
From students as SGMaths,
students as SGPhysics,
students as SGChemistry
Where SGMaths.student = SGPhysics.student
And SGPhysics.student = SGChemistry.student
And SGMaths.subject = 'math'
And SGMaths.grade > 85
And SGPhysics.subject = 'physics'
And SGPhysics.grade > 85
And SGChemistry.subject = 'chemistry'
And SGChemistry.grade > 85
--David Portas soltion
Select student
From students
Where subject In ('math','physics','chemistry')
And grade > 85
Group By student
Having Count(*) = 3;
--Erland Sommarskog solution
Select Distinct
s.student
From students s
Where Not exists (Select *
From students g
Where g.student = s.student
And g.subject in ('math', 'phyiscs', 'chemistry')
And g.grade <= 85)
Set statistics io on
--Colin's Solution
Select
s.student
From (
Select
student,
max( case when subject = 'math' then grade end ) math,
max( case when subject = 'physics' then grade end ) physics,
max( case when subject = 'chemistry' then grade end ) chemistry
From students
Group by student
) s
where s.math > 85
and s.physics > 85
and s.chemistry > 85
Go
--David Portas soltion
Select student
From students
Where subject In ('math','physics','chemistry')
And grade > 85
Group By student
Having Count(*) = 3;
