I'm stuck on query

Hi guy's its me the newbee again.
I am stuck on a query statement.

I am working on a Database for ancient artifacts and this is the case:

I have a table holding cultures: i.e. roman, Celtic, Greek, etc...

like:

CultID Culture
1 Roman
2 Greek
3 Celtic

I have a table holding maincategory of objects i.e. coins, pottery, glaswork, jewelery, etc...

like:

catID cat
1 Coins
2 Pottery
3 Glass

I have a table holding types of objects i.e. Vase, Bowl,Potin, Denarius, Neckless. These are actually subcategories while i.e. Vase, Bowl are part of Pottery, Potin, Denarius are part of Coins etc... This table therefore also holds the maincategory ID like:

subcatID subcat maincat
1 Denarius 1
2 Potin 1
3 Vase 2
4 bowl 2
5 sestertius 1


While i.e. a Denarius is a typical roman item and Potin Celtic I have a table which holds cultureID and Type ID

Like:

sub2cultID CultID SubID
1 1 1
2 1 5
3 1 3
4 1 4
5 2 3
6 2 4
7 3 2
8 3 3
9 3 4

Now i would like to make a query where:

if I select a culture and then category

it filters the subcategory based on the last table so:

if culture = roman (1) and Maincategory=Coins(1)

it only shows:

1 denarius
3 Vase
4 Bowl
5 sestertius

or:
if culture = Celtic (3) and maincategory = Pottery(2)

it only shows:

3 Vase
4 Bowl



If anybody knows how to make this query I would be really happy, While I am stuck now and it is important to me! So your help is greatly appreciated

it is also ok if it can be done by making 2 queries, and if it can't be done at all in MS SQL please tell me also because i'am stuck now and can't continue my work on this database before this is resolved

Here you go

use tempdb

create table culture(CultID int, Culture varchar(10))
insert into culture select 1, 'Roman'
insert into culture select 2, 'Greek'
insert into culture select 3, 'Celtic'

create table category(CatID int, Category varchar(10))

insert into category select 1, 'Coins'
insert into category select 2,'Pottery'
insert into category select 3, 'Glass'

create table subcategory(subCatID int, subCategory varchar(10),category int)

insert into subcategory select 1, 'Denarius' ,1
insert into subcategory select 2, 'Potin' ,1
insert into subcategory select 3, 'Vase' ,2
insert into subcategory select 4, 'bowl' ,2
insert into subcategory select 5, 'sestertius', 1

create table relation (sub2cultID int, CultID int, subCatID int)
insert into relation select 1, 1, 1
insert into relation select 2, 1, 5
insert into relation select 3, 1, 3
insert into relation select 4, 1, 4
insert into relation select 5, 2, 3
insert into relation select 6, 2, 4
insert into relation select 7, 3, 2
insert into relation select 8, 3, 3
insert into relation select 9, 3, 4



select d.sub2cultid, a.culture,c.subcategory from relation d, culture a, subcategory c , category b where
d.cultid = a.cultid and d.subcatid=c.subcatid and b.catid = c.category
and a.culture ='roman' and b.category='coins'

or

select d.sub2cultid, a.culture,c.subcategory from relation d, culture a, subcategory c , category b where
d.cultid = a.cultid and d.subcatid=c.subcatid and b.catid = c.category
and a.cultid =1 and b.catid=1

-----------------------

select d.sub2cultid, a.culture,c.subcategory from relation d, culture a, subcategory c , category b where
d.cultid = a.cultid and d.subcatid=c.subcatid and b.catid = c.category
and a.culture ='celtic' and b.category='pottery'

or

select d.sub2cultid, a.culture,c.subcategory from relation d, culture a, subcategory c , category b where
d.cultid = a.cultid and d.subcatid=c.subcatid and b.catid = c.category
and a.cultid =3 and b.catid=2

when I get to america, someone called MAK is gonna get a blue leotard, red underpants and a red cape in their mailbox.

oh yes

thanx alot, really greatfull ;) :p couldn't done it myself.

hehehehe.. you are funny. dont make me blush.:p