CODEWITHSUNDEEP
sql-server
RaviKant Hudda
RaviKant Hudda

Reputation: 1042

How to get non matching records in sql server in same table?

I have a table say StudentBillDetails and in this table data is saved annually and yrid is referenced to some other table. Now I am stuck with a problem. I want to retrieve non matching records as described below.

Stid    BillNo  Yrid
1        525    3
1        525    1
1        525    4
2        443    4
2        442    1
2        443    3

In above given table structure as you can see for three years StId 1 has same value but StId 2 has a confliction in Yrid 1. So I want to get these type of records.

Upvotes: 1

Views: 316

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

If you just want to flag Stid values which have conflicts then the following simple query should work:

SELECT Stid
FROM yourTable
GROUP BY Stid
HAVING COUNT(DISTINCT BillNo) > 1

If you want the entire records you could try joining your table to the above query:

SELECT t1.*
FROM yourTable t1
INNER JOIN
( SELECT Stid FROM yourTable GROUP BY Stid HAVING COUNT(DISTINCT BillNo) > 1 ) t2
    ON t1.Stid = t2.Stid

Upvotes: 1

Related Questions