1

I have a table like this:

id  user             userid  user_id  ipaddr
1   'Client'         10      0        1.1.1.1
2   'admin1'         1       0        2.2.2.2
3   'Client'         3       0        1.1.1.1
4   'admin2'         5       0        3.3.3.3
5   'Client'         12      0        4.4.4.4
6   'admin1'         1       0        2.2.2.2
7   'Client'         21      0        4.4.4.4
8   'Client'         25      0        6.6.6.6
9   'Sub-Client 20'  35      0        4.4.4.4

I'm going to run a query which finds whic:

find in `ipaddr` column and find any ip-addresses are the same for other `user`s which names are `Client` or `Sub-Client`.

I hope I could've explained clear.

Suppose this:

ip-address 1.1.1.1 ==> Clients with userid 10, 3.
ip-address 4.4.4.4 ==> Clients with userid 12, 21, 35.

It shows these clients have logged in with the exact same IP.

I don't know what query to run, and I even don't know what's this called, so that I can google.

1 Answer 1

1
  • A GROUP BYwould eliminate the duplicates.
  • the GROUP_CONCAT produces the string of userids
  • THE WHERE reduces to the wanted user type
  • And HAVING checks that only duplcate rows are in th result set

The result looks then like

CREATE TABLE tab (
  `id` INTEGER,
  `user` VARCHAR(15),
  `userid` INTEGER,
  `user_id` INTEGER,
  `ipaddr` VARCHAR(7)
);

INSERT INTO tab
  (`id`, `user`, `userid`, `user_id`, `ipaddr`)
VALUES
  ('1', 'Client', '10', '0', '1.1.1.1'),
  ('2', 'admin1', '1', '0', '2.2.2.2'),
  ('3', 'Client', '3', '0', '1.1.1.1'),
  ('4', 'admin2', '5', '0', '3.3.3.3'),
  ('5', 'Client', '12', '0', '4.4.4.4'),
  ('6', 'admin1', '1', '0', '2.2.2.2'),
  ('7', 'Client', '21', '0', '4.4.4.4'),
  ('8', 'Client', '25', '0', '6.6.6.6'),
  ('9', 'Sub-Client 20', '35', '0', '4.4.4.4');
SELECT
  `ipaddr`, GROUP_CONCAT(`userid`  ORDER BY `userid` ASC  ) userid
FROM tab 
WHERE `user` = 'Client' OR `user` LIKE 'Sub-Client%'
GROUP BY `ipaddr`
HAVING COUNT(*) > 1
ORDER By `ipaddr`
ipaddr userid
1.1.1.1 3,10
4.4.4.4 12,21,35

fiddle

2
  • Thanks a lot my friend. Oops, I forgot to add some duplicate userid because one user may log in multiple times. dbfiddle.uk/AkANIkoH Do you please check it?
    – Saeed
    Mar 17 at 3:58
  • 1
    Grou_concar supports distinct see dbfiddle.uk/jyUW0AhA
    – nbk
    Mar 17 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.