Add fields to a select

Question

Add fields to a select

Navigation

#1 by (2 votes)

1

How do you put the driver and the idle in my SELECT:

CREATE TABLE Obra_Assunto (
            idassunto INTEGER NOT NULL,
            idobra INTEGER NOT NULL,
            PRIMARY KEY (idassunto, idobra),
            FOREIGN KEY (idobra)REFERENCES Obra (idobra),
            FOREIGN KEY (idassunto)REFERENCES Assunto (idassunto)
            );

CREATE TABLE Obra_Autor (
            idobra INTEGER NOT NULL,
            idautor INTEGER NOT NULL,
            PRIMARY KEY (idobra, idautor),
            FOREIGN KEY (idobra)REFERENCES Obra (idobra),
            FOREIGN KEY (idautor)REFERENCES Autor (idautor)
            );

CREATE TABLE Assunto (
            idassunto INTEGER NOT NULL,
            descricaoAssunto VARCHAR(50) NOT NULL,
            PRIMARY KEY (idassunto)
            );

CREATE TABLE Autor (
            idautor INTEGER NOT NULL,
            nomeAutor VARCHAR(50) NOT NULL,
            PRIMARY KEY (idautor)
            );


CREATE TABLE Obra (
            idobra INTEGER NOT NULL,
            titulo VARCHAR(50) NOT NULL,
            ano_publicacao INTEGER NOT NULL,
            quantidade INTEGER NOT NULL,
            ideditora INTEGER NOT NULL,
            PRIMARY KEY (idobra),
            FOREIGN KEY (ideditora)REFERENCES Editora (ideditora)
            );

select nomeAutor,titulo,descricaoAssunto 
  from autor a , obra_autor oa , obra o , obra_assunto os, assunto ass
Where a.idautor= oa.idautor AND oa.idobra= o.idobra AND o.idobra= os.idassunto AND os.idassunto= ass.idassunto;

sql query postgresql

asked by anonymous 06.06.2018 / 14:42

1 answer

List Bank result in checkbox with AJAX and JQuery How to block the sending of package-lock.json by gitignore?

score 2 · Answer 1

You've tried something like:

SELECT
    a.idautor,
    a.nomeAutor,
    o.idobra,
    o.titulo,
    ass.descricaoAssunto 
FROM
  autor AS a
JOIN
  obra_autor AS oa ON ( a.idautor = oa.idautor )
JOIN
  obra AS o ON ( oa.idobra = o.idobra )
LEFT JOIN
  obra_assunto AS oas ON ( o.idobra = oas.idobra )
LEFT JOIN
  assunto AS ass ON ( oas.idassunto = ass.idassunto );

The above query will only retrieve the Autores that have Obras registered. If Obras does not have Assuntos registered, the field containing Descrição do Assunto will NULL .

SQLFiddle: link