CODEWITHSUNDEEP
mysqlcountsubstring
user1964248
user1964248

Reputation: 23

find two alike in MySQL database

I want to find out if any rows shares ip. I found out how to do this by using

SELECT * FROM people WHERE ip IN ( SELECT ip FROM people GROUP BY ip HAVING COUNT(ip > 1 ) ORDER BY name

and from this database

John 123.456.78.90
Bob 123.456.78.90
Eric 123.456.78.90

I got

John 123.456.78.90
Eric 123.456.78.90

But after some changes in the database ports were added to the ip's (dont ask me why)
so now the database looks like this:

John 123.456.78.90:1337
Bob 123.456.78.90:4242
Eric 123.456.78.90:9001

I found out I could delete everything after the colon by doing this

SUBSTRING_INDEX(ip,':', 1)

I cant figure out how to delete everything after the colon and then see if anyone has the same ip
any way to mix these two queries together?

Upvotes: 0

Views: 68

Answers (1)

wless1
wless1

Reputation: 3549

You can use the SUBSTRING_INDEX function in the grouping, for example:

SELECT * FROM people 
WHERE SUBSTRING_INDEX(ip, ':', 1) IN ( SELECT SUBSTRING_INDEX(ip, ':', 1) FROM people GROUP BY SUBSTRING_INDEX(ip, ':', 1) HAVING COUNT(ip > 1 ) 
ORDER BY name

Upvotes: 1

Related Questions