# 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?

##### 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
##### Share on other sites

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

##### 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.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.