How to Identify Rows Where Two Columns Have Match Exactly in SQL Server

Select rows where there is a one-to-one match between two columns in same table SQL

create table #one_to_one
(id_1 int, name varchar(20), dt date, id_2 int)

insert into #one_to_one values( 487, 'Joe', '09/06/2004' , 332)
insert into #one_to_one values( 731, 'Mike', '06/01/2004' , 116)
insert into #one_to_one values(487, 'Joe', '09/06/2004' , 354)
insert into #one_to_one values( 777, 'Rich', '01/01/2002', 455)
insert into #one_to_one values( 745, 'Mike', '06/01/2004', 116)


select id_1, name, dt, id_2
from (select *, count(*) over(partition by id_1) as id_1_count,
count(*) over(partition by id_2) as id_2_count
from #one_to_one) res
where id_1_count = 1 and id_2_count = 1;

Verify two columns of two different tables match exactly

Use:

SELECT MAX(x.t1_missing_count) AS t1_missing_count, 
MAX(x.t2_missing_count) AS t2_missing_count
FROM (
SELECT COUNT(DISTINCT t1.col1) AS t1_missing_count,
NULL AS t2_missing_count
FROM TABLE_1 t1
WHERE t1.col1 NOT IN (SELECT DISTINCT t2.col1
FROM TABLE_2 t2)
UNION ALL
SELECT NULL,
COUNT(DISTINCT t2.col1),
FROM TABLE_2 t2
WHERE t2.col1 NOT IN (SELECT DISTINCT t1.col1
FROM TABLE_1 t1)) x

SQL: Compare Columns for Exact Match of sets

Try with this:

Create table t(id int, c char(1))

Insert into t values
(1, 'a'),
(1, 'a'),
(2, 'b'),
(2, null),
(3, null),
(3, null),
(4, 'c'),
(4, 'd')

;with cte as(
select id, count(*) c1, count(c) c2, count(distinct c) c3 from t
group by id)
select t.id, t.c, ca.m from t
Cross apply(select case when c2 = 0 and c3 = 0 then 'empty'
when c1 = c2 and c3 = 1 then 'yes'
else 'no' end as m
from cte where cte.id = t.id) ca

Output:

id  c       m
1 a yes
1 a yes
2 b no
2 (null) no
3 (null) empty
3 (null) empty
4 c no
4 d no

find matching row based on multiple columns (sequence of columns doesn't matter)

this query will make all values of columns (itemID columns) in a single column

   SELECT id,name,  case WHEN itemid1 IS NOT NULL THEN  CONVERT(varchar(10), itemid1) + ','
else '' end + case WHEN itemid2 IS NOT NULL THEN CONVERT(varchar(10), itemid2) + ',' else
'' end + case WHEN itemid3 IS NOT NULL THEN CONVERT(varchar(10), itemid3) + ',' else ''
end + case WHEN itemid4 IS NOT NULL THEN CONVERT(varchar(10), itemid4) + ',' else '' end
as itemid FROM emp

now you have to write a function to order the comma separated values of ItemID column and return the string. thats all.. now you can easily do exact match.. but this won't be giving good performance..

Creating a computed column with same query will get you performance..



Related Topics



Leave a reply



Submit