Sql: How to Get Both Match and Non-Match Records

Sql Query help to get non matching records from two tables

create table #one (id int,acc nvarchar(25))
insert into #one (id , acc) values(1,'one')
insert into #one (id , acc) values(2,'two')
insert into #one (id , acc) values(3,'three')

create table #two (acct nvarchar(25),ids int)
insert into #two (acct,ids) values('one',1)
insert into #two (acct,ids) values('two',3)
insert into #two (acct,ids) values('four',4)

select ids from #two EXCEPT select id from #one

drop table #one
drop table #two

test this one

How to compare two table matching and non matching in sql query?

Hmm . . . If the columns have the same type, then perhaps unpivoting and a full-join for matching. The final step is aggregation:

select col,
(case when count(val) = count(*) then 'Matched'
else 'Non-matched'
end)
from (t1 cross join lateral
(values (t1.data1, 'data1', 1),
(t1.data2, 'data2', 1),
(t1.data3, 'data3', 1)
) v(col, val, which)
) full join
(t2 cross join lateral
(values (t2.data1, 'data1', 2),
(t2.data2, 'data2', 2),
(t2.data3, 'data3', 2)
) v(col, val, which)
)
using (col, val)
group by col;

How to find a list of non-matching records in SQL? (Having some trouble)

You need to join the tables and get the UserIDs that don't have a record in the Events table meeting your rules

SELECT Users.userID 
FROM Users
LEFT JOIN Events ON Users.userID = Events.UserID
AND Events.EventTypeID = 70
AND Events.EventTime >= CONVERT(DATE, GETDATE())
WHERE EventID IS NULL
AND Users.LastAccessTime >= CONVERT(DATE, GETDATE())

Join to grab only non-matching records SQL

I would just use a single query with exists logic:

SELECT *
FROM main_table t1
WHERE record_type = 'NEW' AND
NOT EXISTS (SELECT 1 FROM main_table t2
WHERE t2.id = t1.id AND t2.record_type = 'DEL');

In plain English, the above query says to find all records which are NEW which also do not have associated with the same id another record having DEL.



Related Topics



Leave a reply



Submit