SQL Queries - Join two tables

Question

I have two tables

TABLE 1 - Called Artista (artist) with an ID, Name, first year, second year.

    ID NAME                                                    year1      year2 COUNTRY

    41 Filipe Nobrega                                           2001       2051 Portugal             
    42 Bernardo Morais                                          2010       2060 Portugal             
    43 Fernando Evora                                           2013       2070 Portugal             
    44 Florenzo Giovanni                                        2003       2047 Italia               
    45 Tiago Alves                                              1980       1990 Portugal             
    46 Rui Gonzales                                             1975       1995 Espanha              
    47 Jose Almeida                                             1800       1876 Portugal             
    48 Jhon Snow                                                1900       1940 Winterfell           
    49 test                                                     2001       2020 Espanha

TABLE 2 - Called autoria (author), with the ID of a piece of art and the ID of an artist, also it has the type of art( painting, music, sculpture...)

  ART   ARTIST   TYPE_OF_ART

   121         41 Pintura      
   122         41 Musica       
   123         42 Pintura      
   124         42 Cinema       
   125         42 Literatura   
   126         43 Teatro       
   127         43 Literatura   
   128         43 Danca        
   129         43 Arte_digital 
   130         43 Pintura      
   131         44 Pintura      
   132         44 Cinema       
   133         44 Pintura      
   134         45 Cinema       
   135         45 Literatura   
   136         46 Cinema       
   137         46 Literatura   
   138         46 Literatura   
   139         47 Arte_digital 
   140         47 Pintura      
   141         47 Teatro       
   142         48 Cinema

The problem is: Get all the artists that made less than 2 different pieces of art.

The result should be:

FILIPE NOBREGA - 41 he has 2 pieces of art

TIAGO ALVES - 45 he has 2 pieces of art

JOHN SNOW - 48 he has 1 piece of art

AND TEST - 49 he has 0

This is what I've got:

SELECT DISTINCT A.name, A.id FROM artista A, autoria AUT WHERE AUT.artist = A.id GROUP BY(A.name, A.id) HAVING (COUNT(*) <= 2);

And it returns all of the above except TEST.

Are you using MySQL or Oracle? Don't tag products not involved. — jarlh
– jarlh, Commented Dec 12, 2017 at 21:07
Tag properly!!!! MySQL <> Oracle <> SQL Server. WHICH ONE DO YOU USE??? — Eric
– Eric, Commented Dec 12, 2017 at 21:12

Matt Spinks · Accepted Answer · 2017-12-12 21:09:02Z

1

This query performs an INNER JOIN. You need an OUTER JOIN because autoria may not contain any records that join to Artista. And if it does not contain records that join, then an INNER JOIN does not include those in the result set. Change your query to use an OUTER JOIN:

SELECT DISTINCT A.name, A.id
  FROM artista A LEFT OUTER JOIN autoria AUT ON AUT.artist = A.id
GROUP BY(A.name, A.id)
HAVING (COUNT(*) <= 2);

answered Dec 12, 2017 at 21:09

Matt Spinks

6,7404 gold badges32 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Filipe Nóbrega Over a year ago

Thank you, it totally works, the problem is that I can only use 'things I know' so the LEFT OUTER JOIN is a no no for me. My teacher made it even harder by saying I cant have 2 SELECT's. If u cant find another solution only using GROUP BY and HAVING it would be appreciated.

Matt Spinks Over a year ago

Can you do some research on outer joins to say that you "know" about them now? Also, doing this: SELECT DISTINCT A.name, A.id FROM artista A, autoria AUT WHERE AUT.artist = A.id creates an implicit inner join. So either way, you are using a join.

Filipe Nóbrega Over a year ago

I did speak to my teacher and she said as long as it works. Thx for the help!!

Collectives™ on Stack Overflow

SQL Queries - Join two tables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related