Welcome to WebmasterWorld Guest from 54.147.134.218

Forum Moderators: open

Message Too Old, No Replies

SQL query help

bitwise

     
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:


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)

New User

5+ Year Member

joined:Feb 1, 2010
posts:29
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
posts:1108
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.

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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members