Query with JOIN in 3 tables

Asked

Viewed 150 times

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, only I have no idea how to do it. I’m supposed to use JOIN, only I don’t have much knowledge of this command.

Thank you in advance.

mysql query select join
  • 1

    friend, you did not make confusion as the columns of the table Bookmaker? the column book has null fields, that’s right?

2 answers

2


Thus:

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);

2

In reality 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)

Browser other questions tagged mysql query select join

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