Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006
    Posts
    36

    Smile Unanswered: Taxing query - T-SQL

    Hi guys and girls,
    I've been trying to get my head around this query and can't seem to
    get it right.

    TABLE:
    products (
    catalogid int IDENTITY NOT NULL,
    cname varchar (255),
    cstock int DEFAULT 0)

    PROBLEM:
    I've tried to elaborate as simply as possible...
    This is for a 'music store'
    Look in products table for products where artist name (cname) match
    other 'cname's of the same value.

    If there are more than four records with the same 'cname' then
    check whether the stock(cstock) for each of the records is greater than 1.

    If there are more than 4 records for an artist (cname match) and there
    is 1 or more items in (cstock)stock for each of the first four records then
    return a list containing the 'cname' field for each artist that
    qualifies.

    Would REALLY appreciate some help with this. My T-sql is rubbish...
    Cheers

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select cname
      from products
     where cstock > 0
    group
        by cname
    having count(*) >= 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    36

    Exclamation Cheers!

    Thanks Rudy,
    You're a champion!
    Much appreciated!

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Taxi's are so expensive...I sually use the train/subway in New York...oh, never mind

    (gotta start reading these thing more carefully)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ...and who calls cd's records anymore, I mean I have a lot of vinyl that I like to still spin on the old pioneer turn table, and the sound is so much warmer tha....oh, never mind
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    All I can say is, if the government is going to start taxing queries, I will need to increase my bill-rate.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •