Query help

[screenmates]

I am new to sql and need some help with this query. The tables are arranged as under:

Table name: ARTIST
Columns: ArtistID Genre
Values Sting Pop
Sting Rock
Bryan Rock

Table Name: GENRE
Column: GenreID
Values:

Electronica
Country
Pop
Rock


Table Name: Criteria
Column: CriteriaID
Values:

Pop
Rock

I am trying to use the values in the criteria table to search the artist table to return the name of the artists that have "all" (AND-clause) the criteria values (belong to all the genres in the criteria table). I need just the name of the artist (single row) but I am getting multiples.

Multiple rows (I need just one row for all the criteria values)

select ArtistID
from artist a, criteria c
WHERE a.GenreID = c.GenreID

I don't want an OR-clause:
select ArtistID
from artist a
WHERE a.GenreID in (select genreID from criteria)

thanks

 

[r937]

select A.ArtistID
  from Criteria as C
inner
  join ARTIST as A 
    on C.CriteriaID = A.Genre
group
    by A.ArtistID
having count(distinct A.Genre)
     = ( select count(*) from Criteria )

r937.com | rudy.ca