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!

g11DBhi,

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|||I'm afraid your query won't work :o

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:
Select A,B,C,D,E
From Table1
Where A=B and B=C and (A <> E or A <> D)|||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.|||Thanks for the suggessions|||Here's some NZDF code that will do what you've asked for: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|||Always the skeptic, Pat.|||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...
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? :)|||How about: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