Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

SQL query help




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:

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'.


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 10+ 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.

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