SQL Server Matching & Non Matching records with single query in SQL

ssgvzors 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(119)

I have a table in which it contains duplicate data based on multiple columns.I am looking to select the matching data based on multiple columns and at the same time non matching based on another column.

CREATE TABLE #table1
(
  id   INT,
  code VARCHAR(5),
  type VARCHAR(5)
)

INSERT INTO #table1(id,code,type)
SELECT 1,'AA','T' UNION ALL
SELECT 2,'AB','R' UNION ALL
SELECT 3,'AC','T' UNION ALL
SELECT 1,'AA','K' UNION ALL
SELECT 3,'AC','T' 

    
SELECT t.*
  FROM #table1 T
 INNER JOIN #table1 T2
    ON t.id = t2.id
   AND t.code = t2.code
   AND t.type <> t2.type

I tried by self join with matching fields equal and non matching fields not equal

sql-server

来源：https://stackoverflow.com/questions/77256419/matching-non-matching-records-with-single-query-in-sql

1条答案

按热度按时间

ulydmbyx1#

Try this:

SELECT t1.ID, t1.CODE, t1.TYPE
      FROM Table1 t1
INNER JOIN Table1 t2
        ON t1.ID = t2.ID
       AND t1.CODE = t2.CODE
       AND t1.TYPE <> t2.TYPE

This query will return the rows from Table1 that have matching ID and CODE values but different TYPE values.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Matching & Non Matching records with single query in SQL

1条答案

相关问题

热门标签

最新问答