Attempting to get a table from SQL Request

Question

I'm working on a project right now and I need to do some request to my DB via SQL *PLUS.

Here is what I'm trying to do. I want to get a table in which I get Professor first and last name with those conditons (I have to verify the first condition, and then the other):

• (First) In a session (let's say 12004), a prof did teach those two courses, INF3180 and INF2110

• (Second) In another session, 32003, a prof did teach those two courses, INF1130 and INF1110

Here is the code that created the DB:

CREATE TABLE Professor
(professorCode  CHAR(5) NOT NULL,
 lastName           VARCHAR(10) NOT NULL,
 firstName      VARCHAR(10) NOT NULL,
 CONSTRAINT PrimaryKeyProfessor PRIMARY KEY     (professorCode)
)
;

CREATE TABLE Group
(sigle      CHAR(7)     NOT NULL,
 noGroup    INTEGER     NOT NULL,
 sessionCode INTEGER        NOT NULL,
 maxInscriptions    INTEGER     NOT NULL,
 professorCode  CHAR(5) NOT NULL,
CONSTRAINT PrimaryKeyGroup PRIMARY KEY   
(sigle,noGroupe,sessionCode),
CONSTRAINT CESigleGroupeRefCours FOREIGN KEY    (sigle) REFERENCES Cours,
CONSTRAINT CECodeSessionRefSession FOREIGN KEY  (sessionCode) REFERENCES 
Session,
CONSTRAINT CEcodeProfRefProfessor FOREIGN KEY(professorCode) REFERENCES 
Professor
) 
;

And here is my current not working request :

SELECT DISTINCT Professor.firstName, Professor.lastName
FROM            Professor, Group
WHERE           Group.professorCode = Professor.professorCode
                AND Group.sessionCode = 32003
                AND (Group.sigle = 'INF1130' AND
                     Group.sigle = 'INF1110')
                OR Group.sessionCode = 12004 
                AND (Group.sigle = 'INF3180' AND
                     Group.sigle = 'INF2110')

I know there is a way to combine both results, but I can't seem to find it.

There is only one match possible in that case :

• Only one match with 32003 : INF1130, INF1110
• None match with 12004 : INF3180, INF2110

The resulting table is supposed to look like this :

--------------------------
First Name       Last Name
--------------------------
Denis            Tremblay

The proposed solution given by Gordon Linoff looks very good, except it returns me no table since with the following the code, it needs to have the 4 courses and 2 sessionCode to be included. The issue here is that it needs to verify both condition and append the result. Let's say the conditions for the session 12004 results to nothing, then I can consider it as NULL. Then, the second condition, with the session 32003, gives me one match. It should append both results to give me the table presented over.

I want to do one request only for this.

Thanks A LOT!

EDIT : Reformulated

EDIT2 : Gave an example of a known match

EDIT3 : Further explanation why the proposed solution isn't working

Answer 1

It seems like you want to list any professor who either taught INF1130 and INF1110 in 32003; or taught INF3180 and INF2110 in 12004. Unfortunately you've presented that as AND (i.e. they have to have taught all four courses - one pair of courses AND the other), not OR (one set of courses OR the other).

As a long-winded way of expanding what I think you want:

SELECT p.firstName, p.lastName
FROM Professor p
WHERE (
  EXISTS (
    SELECT *
    FROM GroupX g
    WHERE professorCode = p.professorCode
    AND sessionCode = 32003
    AND sigle = 'INF1130'
  )
  AND EXISTS (
    SELECT *
    FROM GroupX g
    WHERE professorCode = p.professorCode
    AND sessionCode = 32003
    AND sigle = 'INF1110'
  )
)
OR (
  EXISTS (
    SELECT *
    FROM GroupX g
    WHERE professorCode = p.professorCode
    AND sessionCode = 12004
    AND sigle = 'INF3180'
  )
  AND EXISTS (
    SELECT *
    FROM GroupX g
    WHERE professorCode = p.professorCode
    AND sessionCode = 12004
    AND sigle = 'INF2110'
  )
);

Four subqueries isn't going to be terribly efficient. You could do mutiple joins instead.

If you will always be looking for two sigle values per sessionCode then you could modify Gordon's answer to count how many matches each sigle, by adding that to the group-by clause:

SELECT p.firstName, p.lastName
FROM GroupX g
JOIN Professor p
ON p.professorCode = g.professorCode
WHERE (g.sessionCode, g.sigle) IN ( (32003, 'INF1130'), (32003, 'INF1110'),
                                    (12004, 'INF3180'), (12004, 'INF2110')
                                  )
GROUP BY p.firstName, p.lastName, g.sessionCode
HAVING COUNT(*) = 2;

If you did have a professor who taught all four then you would get them listed twice; if that can happen you could add your DISTINCT back in, though that feels a bit wrong. You could also use a subquery and IN to avoid that:

SELECT p.firstName, p.lastName
FROM Professor p
WHERE ProfessorCode IN (
  SELECT professorCode
  FROM GroupX
  WHERE (sessionCode, sigle) IN ( (32003, 'INF1130'), (32003, 'INF1110'),
                                  (12004, 'INF3180'), (12004, 'INF2110')
                                )
  GROUP BY professorCode, sessionCode
  HAVING COUNT(*) = 2
)

(I've changed Group to GroupX because that isn't a valid identifier; because it's a keyword. I assume you've changed your real names - maybe from another language?)

Answer 2

use modern join

SELECT Professor.firstName, Professor.lastName
FROM            Professor join "Group" g on
            g.professorCode = Professor.professorCode
               where  g.sessionCode in( 32003,12004 )
                AND g.sigle in( 'INF1130', 'INF1110','INF3180','INF2110')                        
             group by  Professor.firstName, Professor.lastName
             having count( distinct sigle )=4

Answer 3

Think: group by and having. More importantly, think JOIN, JOIN, JOIN. Never use commas in the from clause.

SELECT p.firstName, p.lastName
FROM Professor p JOIN
     Group g
     ON g.professorCode = p.professorCode
WHERE (g.sessionCode, g.sigle) IN ( (32003, 'INF1130'), (32003, 'INF1110'),
                                    (12004, 'INF3180'), (12004, 'INF2110')
                                  )
GROUP BY p.firstName, p.lastName
HAVING COUNT(DISTINCT g.sigl) = 4;  -- has all four