SQL Query to Find specific output-CodePudding

I have table like below:

SID     GRADE    SUBJECT
1        C        ENG
1        D        PHYSICS
1        E        CHEMISTRY
2        A        BIOLOGY
2        B        ENG
3        A        MATH
4        B        PHYSICS
4        C        MATH
4        D        ENG

Output: If any of the record for a particular SID is having grade B, then consider only that line item, discard other line items for that particular SID. If any of the record for a particular SID isn't having grade B, then output all the line items for that SID.

output will be look like as below:

SID     GRADE    SUBJECT
1        C        ENG
1        D        PHYSICS
1        E        CHEMISTRY
2        B        ENG
3        A        MATH
4        B        PHYSICS

Can anyone please assist me with the SQL Query? I'm using MS SQL Server

CodePudding user response：

The first select gets your B students, the second select gets non-B students, and the union combines the two selects.

SELECT
    SID,
    GRADE,
    SUBJECT
FROM
    YourTable
WHERE 
    GRADE = 'B'
UNION
SELECT
    YourTable.SID,
    YourTable.GRADE,
    YourTable.SUBJECT 
FROM
    YourTable
OUTER APPLY (
        SELECT SID FROM YourTable WHERE GRADE = 'B'
    ) BStudent
WHERE
    YourTable.GRADE <> 'B' AND
    YourTable.SID <> BStudent.SID

CodePudding user response：

You can UNION two queries together, such as:

    --Grade B only
    select *
      from grades
     where grade = 'B'
    union
    --Other people
    select *
      from grades
     where sid not in (select sid from grades where grade = 'B')
    order by sid

The first will return only the Grade-B folks with their Grade-B subjects, and the other query will return the other students.

Db-fiddle found here

CodePudding user response：

you can do it like this (result here)

select * from t1 where grade = 'B'
union
select * from t1 where sid not in (select sid from t1 where grade = 'B')