Have not purchased any of these products

[The Little Guy]

I need to get a list of all the members in a table that have not purchased these products: 14,42,29,86,43,9,44,12,45

 

I thought that this would work but doesn't:

select m.member_id from members m join purchases p on(m.member_id = p.member_id) where and level_id = 3 and is_active and p.product_id not in(14,42,29,86,43,9,44,12,45) group by member_id;

 

I can not figure this out, thanks for any help!

[The Little Guy]

Looks like I got it with this code:

 

select m.member_id from members m join purchases p on(m.member_id = p.member_id) where level_id = 3 and is_active and m.reg_date >= unix_timestamp('2010-01-01') and (
m.member_id not in(select member_id from purchases where product_id = 14) and
m.member_id not in(select member_id from purchases where product_id = 42) and
m.member_id not in(select member_id from purchases where product_id = 29) and
m.member_id not in(select member_id from purchases where product_id = 86) and
m.member_id not in(select member_id from purchases where product_id = 43) and
m.member_id not in(select member_id from purchases where product_id = 9) and
m.member_id not in(select member_id from purchases where product_id = 44) and
m.member_id not in(select member_id from purchases where product_id = 12) and
m.member_id not in(select member_id from purchases where product_id = 45)
) group by member_id;

[gizmola]

I need to get a list of all the members in a table that have not purchased these products: 14,42,29,86,43,9,44,12,45

 

I thought that this would work but doesn't:

select m.member_id from members m join purchases p on(m.member_id = p.member_id) where and level_id = 3 and is_active and p.product_id not in(14,42,29,86,43,9,44,12,45) group by member_id;

 

I can not figure this out, thanks for any help!

 

select m.member_id from members m join purchases p on(m.member_id = p.member_id AND p.product_id not IN (14,42,29,86,43,9,44,12,45)) where level_id = 3 and is_active group by member_id;