Welcome to WebmasterWorld Guest from 54.159.214.250

Forum Moderators: open

SQL query help

bitwise

   
3:35 pm on Jan 22, 2010 (gmt 0)

5+ Year Member



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.

12:58 am on Feb 2, 2010 (gmt 0)

5+ Year Member



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.
1:14 am on Feb 2, 2010 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month