Help with the request

Question

I am trying to make a query that looks at one table to find out if there is a student in the team called CMHT and in the medical team - if there are any, I don’t want to see the result.

I want to see only the record if they are only in CMHT or medic, and not both.

Will the correct direction use a sub-query to filter it? I did a search on NOT IN, but how could you check if it is in more than 2 commands?

Student Team ref 1 CMHT 1 1 Medic 2 2 Medic 3 this would be in the result 3 CMHT 5 this would be in the result 

So far I have executed the following code: do I need to use a subquery or do a self-join and filter it this way?

 SELECT Table1.Student, Table1.Team, Table1.refnumber FROM Table1 WHERE (((Table1.Team) In ('Medics','CMHT')) 

Answer 1

This is Mark Byers answer with a HAVING clause instead of a subquery:

 SELECT Student, Team, ref FROM Table1 GROUP BY Student HAVING COUNT(Student) = 1 

Answer 2

 SELECT * FROM students WHERE NOT EXISTS ( SELECT NULL FROM students si WHERE si.student = s.student AND si.team = 'CMHT' ) OR NOT EXISTS ( SELECT NULL FROM students si WHERE si.student = s.student AND si.team = 'Medic' ) 

Answer 3

 SELECT a.* FROM Table1 a INNER JOIN ( SELECT Student, COUNT(*) FROM Table1 GROUP BY Student HAVING COUNT(*) = 1)b ON (a.Student = b.Student) 

Answer 4

how can you check if 2 or more teams have?

You can count the number of teams per student and then filter only the ones you want to see:

 SELECT student FROM ( SELECT student, COUNT(*) AS cnt FROM Table1 GROUP BY student ) T1 WHERE cnt = 1 

Answer 5

You can do this with an external join.

 select COALESCE(t1.Student, t2.Student) as Student, COALESCE(t1.Team, t2.Team) as Team, COALESCE(t1.ref, t2.ref) as ref from (select * from Student where Team = 'CMHT') t1 outer join (select * from Student where Team = 'Medic') t2 on t1.Student = t2.Student where t1.Student is null or t2.Student is null;