Post reply

SELECT???? Query idea???

  • September 6, 2013 at 3:15 pm

    #395436

    Hello,

    I have 1 table with some fields in it. Two of the fields are important to me.

    I need to check if for each value in field 1 I have more than 1 value in field 2...

    For example:

    Customer Contract

    1 (Peter) A

    2 (John) N

    2 (John) P

    3 (Lisa) S

    So basically how can I identify that for customer John I have 2 contracts N and P?

    Thanks in advance!

  • September 6, 2013 at 3:22 pm

    #1648254

    SELECT *

    FROM dbo.tablename

    WHERE Customer IN (

    SELECT Customer

    FROM dbo.tablename

    GROUP BY Customer

    HAVING COUNT(DISTINCT Contract) > 1

    )

    ORDER BY

    Customer, Contract

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • September 6, 2013 at 3:36 pm

    #1648258

    Thanks a lot!!!

  • September 10, 2013 at 4:06 am

    #1648784

    HI ,

    select * INTO Test FROM

    (

    select ROW_NUMBER()over(partition by customer order by customer) as counts ,customer,Contract

    from tablename

    ) A where counts > 1

    Select * from tablename where customer IN(SELECT customer from Test )

    Hope it helps 🙂 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply