I need a bit of logical help in writing a query.
Here is the set up.
x
create table main_table
(
id varchar(10) not null,
seq varchar(10) not null
)
insert into main_table (id, seq)
values ('A1', '1'), ('A1', '2'),
('A1', '3'), ('A2', '1'),
('A2', '2'), ('A2', '3'),
('A3', '1'), ('A3', '2'),
('A3', '3');
go
create table sub_table
(
id varchar(10) not null,
seq varchar(10) not null
)
insert into sub_table (id, seq)
values ('A1', '1'), ('A1', '2'), ('A2', '1');
I need a query that returns all records from main_table
where the id
of main_table
matches the id
of sub_table
, but seq
of main_table
does not match seq
of sub_table
.
Expected result is
id | seq |
---|---|
A1 | 3 |
A2 | 2 |
A2 | 3 |
My attempt
select
a.id, a.seq
from
main_table a
where
exists (select 1 from sub_table b
where a.id = b.id and a.seq != b.seq)
What is the right query to do this?
Advertisement
Answer
Here’s one way (Fiddle)
SELECT a.id,
a.seq
FROM main_table a
JOIN sub_table b
ON a.id = b.id
GROUP BY a.id,
a.seq
HAVING MAX(CASE WHEN a.seq = b.seq THEN 1 ELSE 0 END) = 0
Or
SELECT a.id,
a.seq
FROM main_table a
WHERE a.id IN (SELECT b.id
FROM sub_table b)
AND NOT EXISTS (SELECT 1
FROM sub_table b
WHERE a.id = b.id
AND a.seq = b.seq)