Results 1 to 3 of 3

Thread: a tricky query

  1. #1
    Join Date
    Mar 2003
    Posts
    144

    Question Unanswered: a tricky query

    Hello

    I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).

    Example:

    (Cod01, 10, 456), (Cod01, 11, 456), (Cod01, 12, 456) <--- Would pass
    (Cod02, 10, 1234), (Cod02, 11, 1234), (Cod02, 12, 1234) <--- Would pass

    (Cod03, 10, 345), (Cod03, 11, 1677), (Cod03, 12, 981) <--- Would not pass


    How can I accomplish that?

    Thanks a lot.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select myTable.Product_ID
         , myTable.Month
         , myTable.Value
      from myTable
    inner
      join (
           select Product_ID
             from myTable
            where Month between 10 and 12
           group
               by Product_ID
           having count(distinct Value)
                = count(*)
           ) as these
        on these.Product_ID = myTable.Product_ID
     where myTable.Month between 10 and 12
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2007
    Posts
    49
    Code:
    Declare @monthStart int
    Declare @monthEnd int
    
    Set @monthStart = 10
    Set @monthEnd = 12
    
    Select myTable.* from myTable
    INNER JOIN 
    (
    Select Product_ID from myTable
    where [month] between @monthStart and @monthEnd
    Group by Product_ID, [Value]
    having count(Product_ID) = ((@monthEnd-@monthStart)+1)
    ) this ON this.Product_ID= myTable.Product_ID
    --------------------------------------------------------------
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.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
  •