-
PLS HLP!!! : 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
Last edited by ronin; 07-01-2003 at 06:11 AM.
-
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
-
IT WORKS!!!
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 couldn't done it myself.
-
hehehehe.. you are funny. dont make me blush.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|