Sql Query To Select Pairs Of Values In A Column 1 Which Match Either One Of Two Patterns In Column 2

My data looks like this: |User ID |Install Version | |--------------------------------| | Value A | Pattern 1 | | Value B | Pattern 1 | | Value A

Solution 1:

I can think of two ways, one with GROUP_CONCAT and the other using subquery.

SELECT user_id, GROUP_CONCAT(installed_version) as all_patterns
FROM your_table
GROUPBY user_id
HAVING all_patterns REGEXP 'Pattern 1'AND all_patterns REGEXP 'Pattern 2';

--

SELECT * FROM 
(
SELECT user_id, installed_version FROM your_table
WHERE installed_version LIKE'Pattern 1'
) as tmp
WHERE installed_version LIKE'Pattern 2';

Solution 2:

The table is not clearly defined, but in principle the query could look similar to this:

SELECT user_id
FROM installation
WHERE version ='version 1'INTERSECTSELECT user_id
FROM installation
WHERE version ='version 2';

Solution 3:

In Postgres, I would go for:

SELECT user_id
FROM t
GROUPBY user_id
HAVINGCOUNT(*) FILTER (WHERE installed_version ='Pattern 1') >0ANDCOUNT(*) FILTER (WHERE installed_version ='Pattern 2') >0

Solution 4:

You can use intersect to get which has both pattern 1 and pattern 2

SELECT [User ID]
FROMTableWHERE [Install Version]='Pattern 1'INTERSECTSELECT [User ID]
FROMTableWHERE [Install Version]='Pattern 2';