How to get record in unique array Mysql

Question

I had a table:

----------------------
| user_id | marker_id|
----------------------
| 1 | 1 |
--------
| 1 |2 |
--------
| 2 |1 |
--------
| 2 |2 |
--------
| 2 |3 |
--------
| 3 |1 |
--------

I want get unique user_id has marker_id =(1,2). Don't get user_id has marker_id = (1,2,3). How to get user_id = 1 and not include user_id =2.

Ankit Agrawal · Accepted Answer · 2016-04-06 11:37:39Z

1

select distinct user_id 
from table_name 
where marker_id in (1,2)

answered Apr 6, 2016 at 11:37

Ankit Agrawal

2,4541 gold badge17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

datnq Over a year ago

But result include user_id =2. I only want get marker_id in (1,2) and total_marker_id = 2. It means user_id has exactly marker_id = (1,2) not more (1,2,3,4)

Ankit Agrawal Over a year ago

please add your expected result in your question

Gordon Linoff · Accepted Answer · 2016-04-06 11:41:07Z

1

One method uses group by:

select user_id
from t
group by user_id
having sum(marker_id = 1) > 0 and
       sum(marker_id = 2) > 0 and
       sum(marker_id not in (1, 2)) = 0;

Each condition in the having clause tests one of the values. The first tests for marker_id = 1 and the > 0 says there is at least one. The second similarly tests for marker_id = 2. The third says that there are no other marker ids.

I call this type of query a "set-within-sets" subquery (you are looking for marker ids for each user). I find that group by/having is a flexible way to solve these queries.

answered Apr 6, 2016 at 11:41

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to get record in unique array Mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related