How to exclude records based on the values of a column in another table?

Analytics

How to exclude records based on the values of a column in another table?

Hi All,

 

I have a table that contains  member id and then I wanted to join to another table using member id and look for the member spending code. A member can have multiple spending code, so what I want to achieve is to remove the member from the fianl result if the member has a spending code 'A1'.

 

 

Here is the example:

 

Member table:

Member_ID

101

102

103

 

Spending table

Member_ID Spending_code

101              B1

101              CC

101              A1

102              CC

102              DD

103             DD

 

My desired result is:

 

Member_id

102

103

 

Could you please help?


Accepted Solutions
Teradata Employee

Re: How to exclude records based on the values of a column in another table?

Hello, 

You can use a query in the where clause to get a list of all member's that you do not want returned. Good luck!

 

drop table mt186048.member_test;
drop table mt186048.spending_test;

create table mt186048.member_test (
	member_id int
) primary index (member_id);

create table mt186048.spending_test (
	member_id int
	,spending_code char(2)
) primary index (member_id);


insert into mt186048.member_test (member_id) values (101);
insert into mt186048.member_test (member_id) values (102);
insert into mt186048.member_test (member_id) values (103);

insert into mt186048.spending_test (member_id, spending_code) values (101,'B1');
insert into mt186048.spending_test (member_id, spending_code) values (101,'CC');
insert into mt186048.spending_test (member_id, spending_code) values (101,'A1');
insert into mt186048.spending_test (member_id, spending_code) values (102,'CC');
insert into mt186048.spending_test (member_id, spending_code) values (102,'DD');
insert into mt186048.spending_test (member_id, spending_code) values (103,'DD');

select distinct member_id
from mt186048.member_test
where member_id not in (
	select distinct member_id
	from mt186048.spending_test
	where spending_code = 'A1'
);
1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: How to exclude records based on the values of a column in another table?

Hello, 

You can use a query in the where clause to get a list of all member's that you do not want returned. Good luck!

 

drop table mt186048.member_test;
drop table mt186048.spending_test;

create table mt186048.member_test (
	member_id int
) primary index (member_id);

create table mt186048.spending_test (
	member_id int
	,spending_code char(2)
) primary index (member_id);


insert into mt186048.member_test (member_id) values (101);
insert into mt186048.member_test (member_id) values (102);
insert into mt186048.member_test (member_id) values (103);

insert into mt186048.spending_test (member_id, spending_code) values (101,'B1');
insert into mt186048.spending_test (member_id, spending_code) values (101,'CC');
insert into mt186048.spending_test (member_id, spending_code) values (101,'A1');
insert into mt186048.spending_test (member_id, spending_code) values (102,'CC');
insert into mt186048.spending_test (member_id, spending_code) values (102,'DD');
insert into mt186048.spending_test (member_id, spending_code) values (103,'DD');

select distinct member_id
from mt186048.member_test
where member_id not in (
	select distinct member_id
	from mt186048.spending_test
	where spending_code = 'A1'
);
Teradata Employee

Re: How to exclude records based on the values of a column in another table?

I prefer to simplify whenever possible:

 

select Member_ID from Member M
join (select distinct Member_ID from Spending
    having min(Spending_code) <> 'A1' group by 1) S
on M.Member_ID = S.Member_ID;

Junior Contributor

Re: How to exclude records based on the values of a column in another table?

But this will fail when your looking for B1 instead of A1 unless you add a CASE, but then it's not simplifyied code anymore :)

 

Why not a good ol'

SELECT member_id
FROM member_test AS m
WHERE NOT EXISTS
 (
   SELECT *
   FROM spending_test AS s
   WHERE s.member_id = m.member_id 
     AND s.spending_code = 'A1'
 );