homepage Welcome to WebmasterWorld Guest from 54.205.59.78
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved