Operation of a right or left in multiple join (Postgres)

Question

Operation of a right or left in multiple join (Postgres)

Navigation

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

0

I am trying to create a query for a library database that meets the following requirements: the number of books written by authors of Spanish nationality who they lived in the second half of the 20th century by editorial, together with the name of the editorial. All publishers must appear, even if they did not publish any author of Spanish nationality.

The problem is that I have to do three joins to get the requested data and in doing so I am having problems to stay with all the publishers even if they have not published for any Spanish author, I have tried to obtain it with a right join but it does not seem function... This is what I have, everything works except that I do not return publishers with null authors:

select count(titulo), nombre, editorial
from libro join autor
on libro.autor = autor.codigo
right join edicion 
on edicion.libro = libro.codigo
where nacionalidad = 'Española' and fecha_nacimiento > '1950-01-01'
group by nombre, editorial

What can be failing?

sql join postgresql

asked by Steve Jade 19.10.2017 в 19:49

source

2 answers

-1

LEFT JOIN retrieves information even if a field is null

    SELECT count(titulo), nombre, editorial
    FROM libro 
    LEFT JOIN autor ON libro.autor = autor.codigo
    LEFT JOIN edicion ON edicion.libro = libro.codigo
    WHERE nacionalidad = 'Española' AND fecha_nacimiento > '1950-01-01'
    GROUP BY nombre, editorial

or you can try to see that you have no problems with the RIGHT JOIN pon INNER JOIN

    SELECT count(titulo), nombre, editorial
    FROM libro 
    LEFT JOIN autor ON libro.autor = autor.codigo
    INNER JOIN edicion ON edicion.libro = libro.codigo
    WHERE nacionalidad = 'Española' AND fecha_nacimiento > '1950-01-01'
    GROUP BY nombre, editorial

answered by 19.10.2017 в 19:56

Problem when signing APK Problems accessing variables with the FileReader

score 1 · Accepted Answer

There are several ways to do this. For example, you can move the conditions of WHERE within the conditions of JOIN :

SELECT  COUNT(DISTINCT l.titulo), 
        e.nombre, 
        e.editorial
FROM edicion e
LEFT JOIN libro l
    ON e.libro = l.codigo
LEFT JOIN autor a
    ON l.autor = a.codigo
    AND a.nacionalidad = 'Española' 
    AND a.fecha_nacimiento > '1950-01-01'
GROUP BY e.nombre, 
         e.editorial
;

Yes, I'm using LEFT JOIN because my brain can not use RIGHT JOIN s for any reason.

Another way is to not use the conditions of the JOIN , and to put it within an expression CASE :

SELECT  COUNT(DISTINCT  CASE 
                            WHEN a.nacionalidad = 'Española' 
                            AND a.fecha_nacimiento > '1950-01-01' 
                            THEN l.titulo
                        END), 
        e.nombre, 
        e.editorial
FROM edicion e
LEFT JOIN libro l
    ON e.libro = l.codigo
LEFT JOIN autor a
    ON l.autor = a.codigo
GROUP BY e.nombre, 
         e.editorial
;

Another way would be to make JOIN with a derived table

SELECT  COUNT(DISTINCT l.titulo), 
        e.nombre, 
        e.editorial
FROM edicion e
LEFT JOIN libro l
    ON e.libro = l.codigo
LEFT JOIN ( SELECT *
            FROM autor
            WHERE a.nacionalidad = 'Española' 
            AND a.fecha_nacimiento > '1950-01-01') a
    ON l.autor = a.codigo
GROUP BY e.nombre, 
         e.editorial
;

In short, there are more ways, but I think these are enough.