SELECT IN A RELATIONSHIP WITH SELF RELATIONSHIP(Verifying Conflicts)

Question

I’m having trouble making an appointment. The situation is as follows, I need the bank to return all the names of the disciplines that have no prerequisites and that are not prerequisites.

This is the relationship:

/* Create a table */
CREATE TABLE DISCIPLINA(CodD char(2) PRIMARY KEY, 
NomeD varchar(60), 
CargaD int, 
AreaD varchar(60), 
PreReqD char(2),
FOREIGN KEY (PreReqD) REFERENCES DISCIPLINA(CodD));


/* Create few records in this table */
INSERT INTO DISCIPLINA VALUES('D1','TLP1',          2,  'Computação',   'D2');
INSERT INTO DISCIPLINA VALUES('D2','Cálculo 1',     4,  'Matemática',   NULL);
INSERT INTO DISCIPLINA VALUES('D3','Inglês',        2,  'Humanas',      NULL);
INSERT INTO DISCIPLINA VALUES('D4','Ed Física',     3,  'Saúde',        NULL);
INSERT INTO DISCIPLINA VALUES('D5','G Analítica',   5,  'Matemática',   'D2');
INSERT INTO DISCIPLINA VALUES('D6','Projeto Final', 6,   NULL,          'D1');

I tried this way and also with LEFT JOIN, but did not succeed.

SELECT DISTINCT D1.NomeD
FROM DISCIPLINA D1, DISCIPLINA D2
WHERE D1.CodD != D2.PreReqD AND D2.PreReqD IS NULL;

Answer 1

You can do it with LEFT JOIN more DISTINCT

SELECT DISTINCT D.NomeD
  FROM Disciplina D
  LEFT JOIN Disciplina DPai ON DPai.PreReqD = D.CodD
 WHERE D.PreReqD IS NULL AND DPai.CodD IS NULL

UPDATE:

Selects only subject names differently

SELECT DISTINCT D.NomeD

Of the tables of disciplines

  FROM Disciplina D 

That has (JOIN) or not (LEFT) relationship with other disciplines (PAI) that has the main discipline (FILHA) as Prérequesito ( Dpai.Prereqd = D.Codd )

  LEFT JOIN Disciplina DPai ON DPai.PreReqD = D.CodD

Where the discipline has no prerequisites, and is not a prerequisite for any other discipline

 WHERE D.PreReqD IS NULL AND DPai.CodD IS NULL

Helped? :)

See working on Sqlfiddle

Answer 2

Try:

 SELECT NomeD
FROM DISCIPLINA d
WHERE d.PreReqD IS NULL
  AND NOT EXISTS
    (SELECT 1
     FROM DISCIPLINA d1
     WHERE d1.PreReqD = d.CodD)

See working on Sqlfiddle.

Answer 3

  SELECT D1.NomeD,  D2.NomeD
    FROM disciplina D1, disciplina D2
      WHERE  D2.codD = D1.PrereqD ;