Help with querying specific data

Question

Hello!

I have encountered some problems with query.

I have three tables, each of which has a column.

They look like this.

table1	table2	table3
column1	column2	column3
SS5F	SS5F	SS8D
SS8D	QA8A	KH6C
KH6C	BC5A	SA6S
QA8A	KH6C	IY1K
SA6S	SA6S	QA8A
LJ6V		BC5A
IY1K
BC5A

 

I need a query to find the data in table1, but not in table2 and table3.

For example, in the example data given above, I want to get 'LJ6V' in table1.

Thank you in advance.

Answer 1

Hi @Annie999

If I understand correctly, you can try this query.

create table table1(column1 varchar(max));
create table table2(column2 varchar(max));
create table table3(column3 varchar(max));
insert into table1 values
('SS5F'),('SS8D'),('KH6C'),('QA8A'),('SA6S'),('LJ6V'),('IY1K'),('BC5A');
insert into table2 values
('SS5F'),('QA8A'),('BC5A'),('KH6C'),('SA6S');
insert into table3 values
('SS8D'),('KH6C'),('SA6S'),('IY1K'),('QA8A'),('BC5A');


;with CTE as(
  select column2 as uni from table2
  union 
  select column3 from table3)
select column1 as result from table1 as t1
where not exists (select uni from CTE as t2 where t1.column1 = t2.uni);

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

select column1 as result from table1

except

select column2 as result from table2

except

select column3 as result from table3