SQL Select Groups Based on Criterias-CodePudding

TABLE1
    STUDENT SUBJECT DATE
    1   ENGLISH 2/14/2021
    1   ENGLISH 4/24/2021
    1   ENGLISH 9/20/2022
    1   ENGLISH 11/14/2022
    1   HISTORY 12/15/2021
    1   HISTORY 3/3/2022
    1   HISTORY 3/12/2022
    2   ENGLISH 1/29/2021
    2   ENGLISH 4/20/2021
    2   ENGLISH 8/29/2022
    2   ART 3/24/2021
    3   ART 6/21/2019
    3  HISTORY 9/3/2022
    4   HISTORY 3/1/2022

I have TABLE1 and wish to make TABLE2 where I include everything but only STUDENT who ever had any ENGLISH. So it look like

TABLE2
    STUDENT SUBJECT DATE
    1   ENGLISH 2/14/2021
    1   ENGLISH 4/24/2021
    1   ENGLISH 9/20/2022
    1   ENGLISH 11/14/2022
    1   HISTORY 12/15/2021
    1   HISTORY 3/3/2022
    1   HISTORY 3/12/2022
    2   ENGLISH 1/29/2021
    2   ENGLISH 4/20/2021
    2   ENGLISH 8/29/2022
    2   ART 3/24/2021

I try:

SELECT * FROM TABLE1
GROUP BY STUDENT
HAVING COUNT(ENGLISH) <> 0;

CodePudding user response：

To get the results that you showed in TABLE2 try:

SELECT
 *
FROM TABLE1
WHERE
  STUDENT IN (SELECT DISTINCT STUDENT FROM TABLE1 WHERE SUBJECT = 'ENGLISH')

CodePudding user response：

use the EXISTS operator to check if there are any rows in the group with the subject ENGLISH.

SELECT *
FROM TABLE1
GROUP BY STUDENT
HAVING EXISTS (SELECT 1 FROM TABLE1 WHERE SUBJECT = 'ENGLISH' AND STUDENT = TABLE1.STUDENT);

CodePudding user response：

I see no reason for grouping here, so here's two alternatives. The second will probably be more performant if there are many rows of data.

select * 
from table1 A
where exists (
    select * from table1 B
        where A.Student = B.student
        and B.subject = 'ENGLISH')



select * 
from table1 A
inner join
(
    select distinct student 
    from Table1
    where subject = 'ENGLISH'
) B
on A.student = B.student;

CodePudding user response：

I think it's easier to select the necessary students in a subquery and add this records with them to WHERE

Using IN you can add a list ('one', 'two', 'three') or query as in the example:

SELECT * 
FROM TABLE1
WHERE STUDENT IN (SELECT STUDENT FROM TABLE1 WHERE SUBJECT = 'ENGLISH')