Results 1 to 4 of 4

Thread: I'm stuck on query

  1. #1
    Join Date
    Jun 2003
    Location
    netherlands
    Posts
    7

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  3. #3
    Join Date
    Jun 2003
    Location
    netherlands
    Posts
    7

    Thumbs up 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.

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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
  •