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
Check If a Time Is Between Two Times (Time Datatype)
Sql Server - Join Rows into Comma Separated List
Sql - Inserting a Row and Returning Primary Key
Run a Join Statement That Excludes Duplicate Rows
Sql71501 - How to Get Rid of This Error
Explode and Implode Strings in MySQL Query
Daily Report by Date With Mssql for Mutiple Column
Sql Method to Replace Repeating Blanks With Single Blanks
Select Column Based on Column Name Stored in Another Table
How Select Max(Salary) of Employee Each Department With Employee_Id and Emp_Name
Pg_Restore: [Archiver] Unsupported Version (1.14) in File Header
Ssis Failed Validation and Returned Validation Status "Vs_Isbroken"
How to Resolve MySQL Error Code: 1292. Truncated Incorrect Double Value
Joining the Table Conditionally in SQL
Select Every Employee That Has a Higher Salary Than the Average of His Department
Sqlcmd Not Able to Find a Library (Libmsodbcsql-17.0.So.1.1) That Is There
Oracle SQL Get the First and Last Records from an Ordered Dataset