SQL Statement Help

[Guest]

I need some help with a SQL statement (SQL Server 2k).

I have three tables, an Artists table which holds user info, a Types table
which holds the TypeID and Description for different types (I.e. visual,
musician, etc) and an Artists_Types table, which stores Type_ID information
for each Artist _ID. Each Artist can have more than one type.

Artists Table:
Artist_ID|First_Name|Last_Name....
1 |Joes |Blogs

Types Table:
Type_ID | Desc
1 | Visual Artist
2 | Digital Artist

Artists_Types Table:
Artist_ID(fk)|Type_ID(fk)
1 | 2
1 | 3
1 | 5
2 | 2
2 | 6


I have a checkbox list that allows the users to select these type IDs. I
now want to query Artists_Types table for an Artist_ID that has ALL of the
types they select. For example, if they select types 2, 3, and 5 it should
return Artist_ID 1 because it has all three. The Select statement will need
to join the Artist and Artists_Types tables.

Any help would be greatly appreciated.

Thanks,
Nathan

 

[WJ]

create table myTable
(
Artist_ID int
,Type_Checked int
)

declare @all int
set @all=3

insert myTable
select
a.Artist_ID
,count(b.Type_ID)
from Artist a
,Artists_Types b
,Types c
where a.Artist_ID=b.Artist_ID
and b.Type_ID=c.Type_ID
group by Artist_ID

Select Artist_ID from myTable where Type_Checked=@all
go

 

[WJ]

group by Artist_ID

GROUP BY A.ARTIST_ID instead

John Webb