using set operations sql to find common elements

Question

Retrieve IDs, first names, and last names for those artists who have done both paintings and drawings

I have tried Joining the tables with:

SELECT artist.artist_id, artist.last_name, artist.first_name FROM artist
INTERSECT
SELECT artwork.artist_id 
from artwork
where  technique ='drawing' and technique= 'painting'

However, the tables must have the same number of columns.

Sample data is better presented as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables. — , Sep 24 '20 at 08:29

score 0 · Answer 1 · answered Sep 24 '20 at 08:37

0

One option uses aggregation:

select a.*
from artist a
inner join artwork aw on aw.artist_id = a.artist_id
where aw.technique in ('painting', 'drawing')
group by a.artist_id
having count(distinct aw.technique) = 2

You can also use exists:

select a.*
from artist a
where exists (select 1 from artwork aw where aw.artist_id = a.artist_id and aw.technique = 'painting')
  and exists (select 1 from artwork aw where aw.artist_id = a.artist_id and aw.technique = 'drawing')

answered Sep 24 '20 at 08:37

GMB

216,147
25
84
135

Thanks a lot! One more question: How can I select these who done only painting and not drawing? – mitchelldom Sep 24 '20 at 09:53
@mitchelldom: starting from the second query, you can just change the second `not exists` to `exists`. As for the first query, you can change the `having` clause from `having count(distinct aw.technique) = 2` to `having min(aw.technique) = max(aw.technique) and min(aw.technique) = 'painting'`. – GMB Sep 24 '20 at 13:13

score 0 · Answer 2 · answered Sep 24 '20 at 11:20

You can use intersect, but this is on the artwork table. So the following returns the artists who have both drawings and paintings:

select aw.artist_id 
from artwork aw
where aw.technique = 'drawing' 
intersect
select aw.artist_id 
from artwork aw
where aw.technique = 'painting' ;

To get the artist information you would use join, exists, or in:

select a.*
from artist a
where a.artist_id in (select aw.artist_id 
                      from artwork aw
                      where aw.technique = 'drawing' 
                      intersect
                      select aw.artist_id 
                      from artwork aw
                      where aw.technique = 'painting'
                     );

There are other methods, such as conditional aggregation. I would be inclined to use:

select a.*
from artist a join
     artwork aw
     using (artist_id)
where aw.technique in ('drawing', 'painting')
group by a.artist_id   -- presumably this is a primary key
having count(distinct aw.technique) = 2;

Or the exists method that GMB suggests.

using set operations sql to find common elements

2 Answers2