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)

Junior Member

5+ Year Member

joined:June 17, 2008
posts: 47
votes: 0

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)

New User

5+ Year Member

joined:Feb 1, 2010
votes: 0

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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
votes: 0

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.

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members