CODEWITHSUNDEEP
sqloracle-databaseinner-join
idopinnn
idopinnn

Reputation: 21

SQL - inner join (where,and,not)

I have 2 tables:

1) STUDENTS-> primary key - id (int)
                     - firstName
                     - lastName
                     - city
2) GRADES->   primary key - studentID (int)
              primary key - courseID ('e' or 'h' or 'm')
                     - grade

I'm trying to select student names that took the course 'm' and didn't take the course 'h' or 'e'.

My solution is:

SELECT  * 
FROM STUDENTS S
INNER JOIN GRADES G1 ON S.ID = G1.STUDENTID
INNER JOIN GRADES G2 ON G1.COURSEID != G2.COURSEID 
                     AND G1.STUDENTID = G1.STUDENTID
WHERE (G1.COURSEID = 'Math ' 
       AND NOT (G2.COURSEID = 'Eng  ' OR G2.COURSEID = 'Heb'))

Any idea what is wrong?

Upvotes: -1

Views: 78

Answers (6)

d r
d r

Reputation: 7846

One option is to use LISTAGG() analytic function to get all courses per student.

WITH    -- S a m p l e    D a t a :
  STUDENTS ( ID, FIRST_NAME, LAST_NAME, CITY ) As
    ( Select 1, 'John', 'Doe', 'NY' From Dual Union All 
      Select 2, 'Jane', 'Doe', 'NY' From Dual Union All 
      Select 3, 'Bob', 'Smith', 'SF' From Dual Union All 
      Select 4, 'Mike', 'Dowson', 'LA' From Dual Union All 
      Select 5, 'Kate', 'Gillmore', 'SF' From Dual 
    ), 
  GRADES ( STUDENT_ID, COURSE_ID, GRADE ) AS 
    ( Select 1, 'h', 4 From Dual Union All 
      Select 1, 'e', 5 From Dual Union All 
      Select 2, 'e', 3 From Dual Union All 
      Select 3, 'm', 5 From Dual Union All 
      Select 3, 'e', 2 From Dual Union All 
      Select 4, 'm', 5 From Dual  
    )

--    S Q L : 
SELECT *
FROM   ( Select       s.*, LISTAGG(g.COURSE_ID, ', ') WITHIN GROUP 
                                  (Order By g.COURSE_ID) 
                                  Over(Partition By s.ID) "COURSES"
         From         STUDENTS s
         Inner Join   GRADES g ON(g.STUDENT_ID = s.ID )
       )
WHERE COURSES = 'm'

/*
ID  FIRST_NAME  LAST_NAME   CITY    COURSES
--  ----------  ----------  ------  -------
 4  Mike        Dowson      LA      m        */

Upvotes: 0

Popeye
Popeye

Reputation: 35920

There are multiple ways of doing it.

You can use NOT EXISTS or GROUP BY ... HAVING as follows:

-- 1

SELECT * FROM STUDENTS S
INNER JOIN GRADES G1 ON S.ID = G1.STUDENTID
WHERE G1.COURSEID = 'Math'
    AND NOT EXISTS (
        SELECT 1 FROM GRADES G2
        WHERE G2.STUDENTID = G1.STUDENTID
          AND G2.COURSEID IN ('Eng','Heb')
    );

-- 2 

SELECT S.ID, S.FIRSTNAME, S.LASTNAME FROM STUDENTS S
       INNER JOIN GRADES G1 ON S.ID = G1.STUDENTID
       WHERE COURSEID IN ('Math','Eng','Heb')
GROUP BY S.ID, S.FIRSTNAME, S.LASTNAME
HAVING SUM(CASE WHEN COURSEID IN ('Eng','Heb') THEN 1 END) = 0
   AND SUM(CASE WHEN COURSEID = 'Math' THEN 1 END) <> 0

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can do aggregation :

select s.id
from student s inner join
     grades g
     on g.studentid = s.studentid
group by s.id
having sum(case when g.courseid in ('eng', 'hub') then 1 else 0 end) = 0 and
       sum(case when g.courseid = 'Math' then 1 else 0 end) = 1;

Upvotes: 0

Anil Kumar
Anil Kumar

Reputation: 84

SELECT a.firstName, a.lastName, b.courseID
FROM STUDENTS a
INNER JOIN GRADES b
ON a.id = b.studentID
AND b.courseID = 'Math'
WHERE a.id NOT IN (
SELECT studentID FROM GRADES b 
WHERE courseID = 'Eng'
OR b.courseID = 'heb')

use this

Upvotes: 0

Aditya Rewari
Aditya Rewari

Reputation: 2707

Another approach using Left Join

SELECT G1.STUDENTID, G1.firstName, G1.COURSEID!
FROM GRADES G1
LEFT JOIN STUDENTS S ON S.ID=G1.STUDENTID
GROUP BY G1.STUDENTID
HAVING G1.COURSEID='Math' AND G1.COURSEID!='Heb' AND G1.COURSEID!='Eng'

Also, you should keep, StudentId at GRADES table as Foreign Key

Upvotes: 0

user7013467
user7013467

Reputation: 

SELECT * 
FROM STUDENTS S 
INNER JOIN GRADES G ON S.ID=G.STUDENTID and G.COURSEID = 'Math '
where s.id not in (
  select STUDENTID from GRADES where COURSEID = 'Eng ' OR G2.COURSEID = 'Heb'
)

Upvotes: 0

Related Questions