Difficulty with a SELECT statement

Question

Post reply

Difficulty with a SELECT statement

DataAnalyst110

Ten Centuries

Points: 1361
More actions
July 27, 2012 at 1:18 pm

#261726

I'm having some trouble evaluating some criteria in my statement. I created a scenario below.
One Stu_ID can have multiple Enr_IDs. What I need returned:
- Anyone with less than 15 Enroll.Credits UNLESS they have another Enroll.Credits that is greater than 15.
- ISIR.DateAdded must not be NULL.
Would someone mind giving some help? Thanks, as always.
CREATE TABLE Enroll
(
Stu_IdCHAR(10)NOT NULL,
Enr_IDCHAR(10)NOT NULL,
CreditsCHAR(10)NOT NULL,
);
INSERT INTO Enroll
(
Stu_Id,
Enr_ID,
Credits
)
Values
('11111', '9876', '40'),
('11111', '7765', '4'),
('22222', '6546', '16'),
('33333', '8975', '3'),
('44444', '5465', '10')
;
CREATE TABLE ISIR
(
Stu_IDCHAR(10)NOT NULL,
DateAddedDateNOT NULL
)
INSERT INTO ISIR
(
Stu_ID,
DateAdded
)
Values
('11111', '2012-01-10'),
('11111', '2012-03-01'),
('22222', '2012-02-09'),
('44444', '2012-04-08')
;
SELECT * FROM Enroll;
SELECT * FROM ISIR

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

wats your expected results based on the sample you provided?

DataAnalyst110 Ten Centuries Points: 1361 More actions · Answer 2

*Stu_ID 11111 doesn't have 15 or more credits on Enr_ID 7765. However, since 11111 has 40 credits associated with Enr_ID 9876, he won't be in the result set.

*22222 does have 15 or more credits, so he is excluded.

*33333 doesn't have 15 or more credits. (Nevertheless, if 33333 did have 15 or more credits he would be

in the result set because ISIR.DateAdded is NULL. So the statement needs to check for that.)

*44444 doesn't have 15 or more credits, so he is included

So the result set will be:

Stu_ID Enr_ID

33333 8975

44444 5465

demonfox SSCertifiable Points: 6289 More actions · Answer 3

WITH test_credit AS(

SELECT * FROM Enroll WHERE Credits>15)

SELECT * FROM Enroll b

LEFT JOIN

ISIR a ON b.Stu_Id=a.Stu_ID

LEFT JOIN test_credit c

ON c.stu_id=a.Stu_ID

WHERE a.DateAdded is null

OR (b.Credits<40)

AND c.credits is null

This should do the work.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

DataAnalyst110 (7/27/2012)
...
UNLESS they have another Enroll.Credits that is greater than 15.

Did you mean SUM(Credits) > 15 or a single course with Credits > 15?

I suspected SUM but coded it each way -- pick the one you meant 🙂

SELECT

e.Stu_Id, e.Enr_ID

FROM dbo.Enroll e

LEFT OUTER JOIN dbo.ISIR i ON

i.Stu_ID = e.Stu_Id

LEFT OUTER JOIN (

SELECT Stu_Id, SUM(CAST(Credits AS int)) AS Credits

FROM dbo.Enroll

GROUP BY Stu_Id

HAVING SUM(CAST(Credits AS int)) >= 15

) AS ec ON

ec.Stu_Id = e.Stu_Id

WHERE

(e.Credits < 15 AND ec.Stu_Id IS NULL) OR

i.DateAdded IS NULL

ORDER BY

e.Stu_Id, e.Enr_ID

SELECT

e.Stu_Id, e.Enr_ID

FROM dbo.Enroll e

LEFT OUTER JOIN dbo.ISIR i ON

i.Stu_ID = e.Stu_Id

WHERE

(e.Credits < 15 AND NOT EXISTS(SELECT * FROM dbo.Enroll e2 WHERE e2.Stu_Id = e.Stu_Id AND e2.Credits >= 15)) OR

i.DateAdded IS NULL

ORDER BY

e.Stu_Id, e.Enr_ID

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

DataAnalyst110 Ten Centuries Points: 1361 More actions · Answer 5

Thanks very much to you both.

Scott, I'm going to go with yours because I can better understand what it is doing. (By the way, I didn't need the SUM, but thanks for putting up both). One question on this part:

WHERE

(e.Credits < 15 AND NOT EXISTS(SELECT * FROM dbo.Enroll e2 WHERE e2.Stu_Id = e.Stu_Id AND e2.Credits >= 15)) OR

i.DateAdded IS NULL

What does the WHERE e2.Stu_Id = e.Stu_Id do? It looks like it just joins the table to itself. If you get a chance, would you (or anyone) mind providing some insight?

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

DataAnalyst110 (7/27/2012)
Thanks very much to you both.
Scott, I'm going to go with yours because I can better understand what it is doing. (By the way, I didn't need the SUM, but thanks for putting up both). One question on this part:
WHERE
(e.Credits < 15 AND NOT EXISTS(SELECT * FROM dbo.Enroll e2 WHERE e2.Stu_Id = e.Stu_Id AND e2.Credits >= 15)) OR
i.DateAdded IS NULL
What does the WHERE e2.Stu_Id = e.Stu_Id do? It looks like it just joins the table to itself. If you get a chance, would you (or anyone) mind providing some insight?

Yes, it joins the same table in the outer/main query (alias e) with the same table in the subquery (alias e2) by matching on Stu_id. This allows the same table as in the outer query to be separately searched in the inner query to see if a Credits value >/>= 15 is found for that student.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lokesh Vij SSChampion Points: 10836 More actions · Answer 7

First of all, your table ISIR should have enr_id added otherwise it does not make any sense to have two dateadded fields for one stu_id

Here is one more way to achieve with current data set:

;WITH dump

AS

(

SELECT e.stu_id, e.enr_id,

CASE dateadded

WHEN dateadded THEN

(CASE

WHEN credits < 15

THEN 1

ELSE 0

END)

ELSE 1

END as flag

FROM enroll e

LEFT JOIN isir i on e.stu_id = i.stu_id

)

SELECT stu_id,enr_id

FROM dump

WHERE flag = 1

GROUP BY stu_id,enr_id

HAVING COUNT(*)=1

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

demonfox SSCertifiable Points: 6289 More actions · Answer 8

Lokesh Vij (7/27/2012)
First of all, your table ISIR should have enr_id added otherwise it does not make any sense to have two dateadded fields for one stu_id
Here is one more way to achieve with current data set:
;WITH dump
AS
(
SELECT e.stu_id, e.enr_id,
CASE dateadded
WHEN dateadded THEN
(CASE
WHEN credits < 15
THEN 1
ELSE 0
END)
ELSE 1
END as flag
FROM enroll e
LEFT JOIN isir i on e.stu_id = i.stu_id
)
SELECT stu_id,enr_id
FROM dump
WHERE flag = 1
GROUP BY stu_id,enr_id
HAVING COUNT(*)=1

this could be an issue when the record have <15 duplicates..

try

UPDATE enroll set credits = 10 where stu_id = 11111

Having Count(*)=1 may let down the expected output..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

demonfox SSCertifiable Points: 6289 More actions · Answer 9

Lokesh Vij (7/27/2012)
First of all, your table ISIR should have enr_id added otherwise it does not make any sense to have two dateadded fields for one stu_id

Yes agree with this one ..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

demonfox SSCertifiable Points: 6289 More actions · Answer 10

demonfox (7/28/2012)
Lokesh Vij (7/27/2012)
First of all, your table ISIR should have enr_id added otherwise it does not make any sense to have two dateadded fields for one stu_id
Yes agree with this one ..

I think this could be possible with enforced business scenarios..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: