finding a subset of data

[lusercfc]

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?

 

[requinix]

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 March 4, 2015 by requinix

[lusercfc]

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