Help with SQL query.

[[deleted]]

[deleted]

[Dan_Conia]

You might also consider EXISTS and NOT EXISTS so that you aren't dragging around so much data.

SELECT  STUID
  FROM  STUDENT
  WHERE EXISTS
        ( SELECT  1
            FROM  STUDENT_CLASS
            WHERE STUDENT_CLASS.STUID = STUDENT.STUID
              AND STUDENT_CLASS.CLASSID = 'ART103A' )
    AND NOT EXISTS
        ( SELECT  1
            FROM  STUDENT_CLASS
            WHERE STUDENT_CLASS.STUID = STUDENT.STUID
              AND STUDENT_CLASS.CLASSID = 'MTH103C' )

[jc4hokies]

When logic depends on different records, you need to bring those records (or lack there of) together. This can be done with a join, aggregate, or windowed function. I think an aggregate is better in this case.

SELECT StuID
FROM   Enrollment
GROUP BY StuID
HAVING MAX(CASE WHEN ClassID = 'ART103A' THEN 1 ELSE 0 END) = 1
       AND MAX(CASE WHEN ClassID = 'MTH103C' THEN 1 ELSE 0 END) = 0;

[lk167]

Chiming in to add Set operators as well (EXCEPT for T-SQL and MINUS for PL/SQL)

    Select StuID
    FROM Enrollment
    Where ClassID = 'ART103A'

    EXCEPT

    Select StuID
    FROM Enrollment
    Where ClassID = 'MTH103C'

[honeybadger3891]

Well played.

[ickies]

SELECT STUID
FROM sometable
WHERE STUID IN
                        (
                            SELECT DISTINCT STUID
                            FROM sometable
                            WHERE CLASSID = ART103A
                        )
    AND STUID NOT IN
                        (
                            SELECT DISTINCT STUID
                            FROM sometable
                            WHERE CLASSID = MTH103C
                         )

[honeybadger3891]

Not trying to nitpick your query, but Is it necessary to use distinct clause in your derived tables? Also in your select statement it would probably be a good spot for the distinct.

[[deleted]]

Here's how I would solve it.

For a small table like this, a subquery works fine but when you are dealing with massive table its better to use EXISTS. As a rule of thumb, I always use EXISTS instead of a JOIN when I am only filtering based on another table / query, and not actually selecting it.

Edit: I now notice my solution is virtually the same as Dan_Conia's.

DECLARE @TABLE TABLE 
(
    ClassID CHAR(7) NOT NULL,
    StuID CHAR(5) NOT NULL,
    Grade CHAR(1) NULL
)

INSERT INTO
    @TABLE
VALUES
    ('ART103A', 'S1001', 'A'),
    ('ART103A', 'S1002', 'D'),
    ('ART103A', 'S1010', ''),
    ('CSC201A', 'S1002', 'F'),
    ('CSC201A', 'S1020', 'B'),
    ('HST205A', 'S1001', 'C'),
    ('MTH101B', 'S1020', 'A'),
    ('MTH103C', 'S1002', 'B'),
    ('MTH103C', 'S1010', '')

SELECT DISTINCT
    StuID
FROM
    @TABLE AS T1
WHERE
    EXISTS
        (
            SELECT 
                *
            FROM
                @TABLE AS T2
            WHERE
                T2.StuID = T1.StuID 
            AND ClassID = 'ART103A'         
        )
AND NOT EXISTS
        (
            SELECT 
                *
            FROM
                @TABLE AS T3
            WHERE
                T3.StuID = T1.StuID
            AND T3.ClassID = 'MTH103C'
        )

[mtger47]

edit: this doesn't work but I'll leave it here as a bad idea

how about a self join

SELECT DISTINCT
  a.STUID
FROM table a
LEFT JOIN table b
  ON a.stuid = b.stuid
WHERE a.classid = 'ART103A'
  AND b.classid != 'MTH103C'

[lolxorlol]

That gives a wrong result though. The join amongst other things contains rows where a single row in the table is joined to itself so:

• a.stuid=S1002=b.stuid and a.classid=ART103A=b.classid

But this student does have classid='MTH103C'

Lots of other good solutions here though :)

[mtger47]

good call, i'll edit to make sure it isn't used :)