Welcome to WebmasterWorld Guest from 22.214.171.124 , register , free tools , login , search , pro membership , help , library , announcements , recent posts , open posts Become a Pro Member
SQL query help bitwise cantona msg:4065745 3:35 pm on Jan 22, 2010 (gmt 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.
5503landis msg:4072380 12:58 am on Feb 2, 2010 (gmt 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. Dijkgraaf msg:4072393 1:14 am on Feb 2, 2010 (gmt 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.