Add fields in a select

Asked

Viewed 207 times

1

As that puts the idautor and idobra in this 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 postgresql query

  • just put select o.idobra, a.idautor ... group by you only use when working with aggregation function

  • Good morning, as well as aggregation function ?

  • aggregation functions: max, sum, min, avg among others....

  • But like this, it only goes for two tables ?

  • hi ? read / watch: https://www.devmedia.com.br/sql-funcoes-de-agregacao/38463

  • But in a table only is simple, I want to know how it does in several tables ?

  • similarly... 1 or n tables, only changes the joins, what you do with the columns, is the same... the answer of lacobus worked ?

  • gave yes , including good response.

  • then mark it as the answer to the question, and if it is valid, rate it as +1

  • access the [Tour] please

Show 5 more comments

1 answer

2

Have you ever 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 recover the Autores who possess Obras registered. If the Obras do not possess Assuntos registered, the field containing the Descrição do Assunto will come NULL.

Sqlfiddle: http://sqlfiddle.com/#! 15/69101/5

  • Hi good morning, I’ve never tried to do something like this , using joins.

  • you are using and do not know, only with other syntax rs

Browser other questions tagged sql postgresql query

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