Question: I've got this table named log:

```
ID   User_ID   Machine_Number   Email 
1     100         12345          jim@gmail.com
2     100         12345          jim@gmail.com
3     101         67890          bill@gmail.com
4     102         12345          steve@gmail.com

```

I need to find the User\_IDs of users with the same Machine\_Number. In this case, I need a query that returns 100 and 102.

I've tried:

```
SELECT user_id, COUNT(machine_number) 
FROM log
GROUP BY machine_number
HAVING COUNT(machine_number) > 1

```

but that gives the count of each occurrence of the machine\_number, i.e.
User\_ID Count(machine\_number)

```
100          2
101          1
102          1

```

Any suggestions?

Answer: 
I guess you want to get both user\_id against same machine. Try `group_concate`:

```
SELECT group_concat(DISTINCT user_id), machine_number
FROM log
GROUP BY machine_number
HAVING COUNT(machine_number) > 1

```
```
Select DISTINCT M.User_Id, M.Machine_NUmber from Machine M  
Inner Join (Select M1.Machine_NUmber from Machine M1 Group By M1.Machine_NUmber Having COUNT(M1.Machine_NUmber)>1) M2 
On M.Machine_NUmber = M2.Machine_NUmber

```