Jump to content
Sign in to follow this  
lusercfc

finding a subset of data

Recommended Posts

This is TimesTen which I'm not really familiar with but it's a flavour of SQL...

 

Think of column_two as a userID and column_three as services they have purchased... so one user may have purchased many items. 

 

What I need to do is find all the users who have purchased services 3 *and* 12... if they've also purchased more (68, 76 etc) thats OK but I need to exclude them if they've purchased 3 *or* 12 (but not both)

 

Here's how I can list all the services (column_three) they have purchased

Command> SELECT * FROM table WHERE column_two = 299993025;
< 234563210, 299993025, 3, N >
< 247146769, 299993025, 12, Y >
< 261197904, 299993025, 68, N >
< 266960384, 299993025, 76, Y >
< 265304246, 299993025, 84, N >
< 248300956, 299993025, 87, Y >
< 231784018, 299993025, 88, Y >
< 201720976, 299993025, 90, Y >
8 rows found.

I modified the query as follows but it doesn't find anything.

Command> SELECT * FROM table WHERE column_2 = 299993025 AND (column_3 = 3 AND column_3 = 12);
0 rows found.

For what it's worth, just having one column_3 item queried does work

Command> SELECT * FROM table WHERE column_2 = 299993025 AND (column_3 = 3);

< 234563210, 299993025, 3, N >

What am I missing?

 

Share this post


Link to post
Share on other sites

A few tactics.

 

My favorite method is

SELECT column_two FROM table WHERE column_three = 3 OR column_three = 12 GROUP BY column_two HAVING COUNT(1) = 2
Assuming no duplicate rows, it will find all matching rows, group by the user, then filter out the groups that don't have two (ie, the number of different matching services) rows.

Only works if you can use just the user ID, though you could use this as a subquery and then JOIN against something else.

 

For a limited number of services, the "normal" way is to do a JOIN

SELECT a.column_two, a.<other columns>, b.<other columns>
FROM table AS a
JOIN table AS b ON a.column_two = b.column_two
WHERE a.column_three = 3 AND b.column_three = 12
The inner JOIN is what restricts the results to users with records for both services; a LEFT or RIGHT JOIN would return those with one or both. Edited by requinix

Share this post


Link to post
Share on other sites

Thanks... the first query worked and that good enough for me.  I really appreciate the explanation too

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.