SQL query help

bitwise

[cantona]

Hi, I need a bit of help with this...

I'm searching for a list of contacts email addresses. The contacts companies have to have an active subscription to product2.
The following query works fine:

SELECT DISTINCT ct.Email
FROM Contacts ct, Companies cp
WHERE ct.CompanyID = cp.ID 
AND cp.Subscription & 16
AND cp.SubscriptionExpiry >= NOW()

Subscription bit 16 means they have access to product2.

But product1 and product2 share the same categories. Let's call the column 'Fruit'.

1=apple
2=pear
4=banana
8=orange
16=melon
32=cherry

The problem is pear and orange are part of product 1.

How can I modify the SQL above to weed out the companies who are only subscribed to pear and orange? (If they have a mixture of product1 and product2 fruits then this is ok).

I want to end up with a list of product2 contacts.

[5503landis]

It's a little late for me, so I may be a bit hazy on what exactly your table structure is. Can you provide a sample list of tables and data so I can get a better visual?

I would think that modifing the query with some type of "where not in (select productX from table) would work, but I can't quite figure out how the tables are laid out.

[Dijkgraaf]

Not 100% sure this is what you are after as you mix your definitions a bit, but you can use this as a basis.

SELECT DISTINCT ct.Email
FROM Contacts ct, Companies cp
WHERE ct.CompanyID = cp.ID
AND (cp.Subscription & 2 OR p.Subscription & 8)
AND NOT (cp.Subscription & 4 OR p.Subscription & 32)
AND cp.SubscriptionExpiry >= NOW()

This will find all subscriptions to pear's and orange, but exclude those that have subscriptions to banana or cherry. It doesn't matter if they have melon or not.