Jump to content

finding a subset of data

Go to solution Solved by requinix,

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?


Link to comment
Share on other sites

  • Solution

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

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.