Query with JOIN in 3 tables

Navigation
1

I have the following tables: 

livroautor

        id  idautor  idlivro  
    ------  -------  ---------
         1        1     (NULL)
         2        2          2
         3        3          3
         4        4          4
         5        5          5
         6        6     (NULL)
         7        7          5
         8        8          2
         9        9          2
        10       10     (NULL)

book 

    id  titulo                      resumo  isbn    paginas  categoria                  
------  --------------------------  ------  ------  -------  ---------------------------
     1  Any Which Way You Can       (NULL)  (NULL)  (NULL)   Comedy                     
     2  Nitro Circus: The Movie     (NULL)  (NULL)  (NULL)   Action|Comedy|Documentary  
     3  Imaginary Heroes            (NULL)  (NULL)  (NULL)   Comedy|Drama               
     4  Nightmare in Las Cruces, A  (NULL)  (NULL)  (NULL)   Documentary                
     5  Boys Next Door, The         (NULL)  (NULL)  (NULL)   Crime|Drama

author 

    id  nome     sobrenome    email                           
------  -------  -----------  --------------------------------
     1  Maddy    Garnsworthy  [email protected]       
     2  Elsy     Kernoghan    [email protected]     
     3  Eduard   Jehan        [email protected]          
     4  Leone    Elizabeth    [email protected]    
     5  Maurita  Ferraron     [email protected]  
     6  Sawyer   Szimoni      [email protected]               
     7  Emmy     Trudgeon     [email protected]           
     8  Wynnie   McKoy        [email protected]               
     9  Buiron   Vian         [email protected]            
    10  Bruis    Naisey       [email protected]

I need to select all books that do not have registered authors, but I have no idea how to do it. It's supposed to use JOIN , but I'm not very aware of this command.

Thank you in advance.

    
asked by anonymous 08.08.2017 / 00:02

2 answers

2

So: 

SELECT l.* 
FROM livro l
INNER JOIN livroautor la on la.idlivro  = l.id
WHERE la.idautor not in(SELECT a.id FROM autor a);
    
08.08.2017 / 00:50
2

In fact you can use the expression NOT EXISTS in this case as follows: 

SELECT l.*
  FROM livro l
 WHERE NOT EXISTS(SELECT 1
                    FROM livroautor la
                   WHERE la.idlivro = l.id)
    
08.08.2017 / 01:05
Exchanging CSS classes with simple JQuery function Visual Studio Installer Projects with PostgreSQL