Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: A, B and C are the same but at least D or E are different (was "noob question")

    hi,

    just wanted to ask the most efficient way to query for the following:
    say i have a table with the following columns A, B, C, D, E.
    i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
    thank you!

    g11DB

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Quote Originally Posted by g11DB
    hi,

    just wanted to ask the most efficient way to query for the following:
    say i have a table with the following columns A, B, C, D, E.
    i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
    thank you!

    g11DB
    is this wat u wanted ?
    Select A,B,C,D,E From Table1 Where Exists (Select 1 from Table1 Where (A=B and B=C) and (( A <>E) or (A <>D)))

    Gurus, please comment
    Cheers....

    baburajv

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm afraid your query won't work

    Firstly, Exists isn't necessary. Secondly, if the condition is true for any of the rows it will display all the rows (the exists statement is not corrolated with the main query). Minor but SELECT * is recommended within an exists statement as the optimser will then chose the best index for the job (doubt it would make much difference here but I like to use it by habit).

    Nearly there - a slight reworking:
    Code:
    Select A,B,C,D,E 
    From Table1 
    Where A=B and B=C and (A <> E or A <> D)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Select A,B,C,D,E From Test
    Where A=B
    and A=C
    and ((A=D or A=E)
    or D<>E)
    hope this works for u.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Thanks for the suggessions
    Cheers....

    baburajv

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Here's some NZDF code that will do what you've asked for:
    Code:
    CREATE TABLE g11DB (
       A		INT
    ,  B		INT
    ,  C		INT
    ,  D		INT
    ,  E		INT
       )
    
    INSERT INTO g11DB (
       A, B, C, D, E)
       SELECT 1, 2, 3, 4, 5 UNION ALL
       SELECT 1, 1, 1, 4, 5 UNION ALL
       SELECT 1, 1, 1, 1, 5 UNION ALL
       SELECT 1, 1, 1, 4, 1 UNION ALL
       SELECT 1, 2, 1, 4, 1
    
    SELECT *
       FROM g11DB
       WHERE 0 = A - B
          AND A = C
          AND A != D
    UNION SELECT *
       FROM g11DB
       WHERE  A = B
          AND -A = -C
          AND 0 != (A - E)
    -PatP

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Always the skeptic, Pat.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jul 2006
    Posts
    111
    Thanks guys but it seems i didn't properly state what I wanted... The code I was looking for is something like this (although i'm still wondering if there's some more efficient version...
    Code:
    SELECT     IPA.*
    FROM         dbo.IRS_PAYMENT IPA INNER JOIN
                    dbo.IRS_PAYMENT IPB ON IPA.ip_employee_id = IPB.ip_employee_id 
                    AND IPA.ip_paydate = IPB.ip_paydate 
                    AND IPA.ip_pay_indicator = IPB.ip_pay_indicator 
                    AND (NOT (IPA.ip_total_tax = IPB.ip_total_tax) 
                    OR NOT (IPA.ip_net_pay = IPA.ip_net_pay))
    ORDER BY IPA.ip_employee_id
    what's NSDF stand for btw?
    Last edited by g11DB; 08-23-06 at 21:48.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about:
    Code:
    SELECT A.*
       FROM dbo.IRS_PAYMENT AS A
       INNER JOIN dbo.IRS_PAYMENT AS B
          ON (B.ip_employee_id = A.ip_employee_id 
          AND B.ip_paydate = A.ip_paydate 
          AND B.ip_pay_indicator = A.ip_pay_indicator)
       WHERE  B.ip_total_tax != A.ip_total_tax
          OR  B.ip_net_pay != A.ip_net_pay
       ORDER BY A.ip_employee_id, A.ip_paydate, A.ip_pay_indicator
    -PatP

Posting Permissions

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