SELECT IN A RELATIONSHIP WITH SELF RELATIONSHIP(Verifying Conflicts)

Asked

Viewed 5,377 times

5

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;
sql sql-server

  • So you only have to return what you have NULL, right?

  • Hello Lucius, in case not. Because Calculus 1 is prerequisite of another matter, then could not return it.

3 answers

3


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

  • Hello Thiago, it worked perfectly. Could you give an explanation of the logic of this Query? Thank you very much for the help.

  • @Wellingtonperez, I did an update with more explanations.

  • Thanks Thiago, I arrived even before Where. Thanks for the explanation!!!

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 on Sqlfiddle.

  • Works, but subquery in WHERE causes the subquery to be executed on each record of each table referenced in FROM. If you still want to, replace NOT IN for NOT EXISTS, will be more performatic.

  • 1

    @Thiagolunardi true, I even asked a question here in the OS that explains it. http://answall.com/questions/62925/not-in-ou-not-exists-qual-usar. Edited question, Thank you.

  • Hi Merconi, thanks for the reply. It worked here too!

  • @Wellingtonperez glad you could help. :)

-1

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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.