SELECT in a RELATIONSHIP WITH SELF-RELATIONSHIP (Checking for Occurrences)

Question

SELECT in a RELATIONSHIP WITH SELF-RELATIONSHIP (Checking for Occurrences)

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (-1 votes)

4

I'm having a hard time making an appointment. The situation is as follows, I need the bank to return all names of courses that have no prerequisites and are not prerequisites.

The relationship is this:

/* 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 I did not succeed.

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

sql sql-server

asked by anonymous 25.11.2015 / 18:39

3 answers

1

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 in SqlFiddle.

25.11.2015 / 18:50

-1

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

02.11.2017 / 19:24

Add days to a date within the method and return it with a new date range How to compile the entire project folder for ES5 using babel.js?

score 2 · Accepted Answer

You can do 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:

Distinguishes disciplinary names only

SELECT DISTINCT D.NomeD

From the subject tables

  FROM Disciplina D

You have (JOIN) or not (LEFT) relation with other disciplines (PAI) that has the main discipline (DAILY) as PreRequisite (DPai.PreReqD = D.CodD)

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

Where the discipline does not have pre-requisites, and is not a prerequisite of any other discipline

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

Did you help? :)

See working in SqlFiddle