SQL Query To Display Mobile number that are registered in Different Mobile Network

database, mysql, sql

Solution

I think you need to use `GROUP BY/HAVING`:

SELECT  MobileNo
FROM    MobileUsers 
GROUP BY MobileNo
HAVING  COUNT(DISTINCT networkname) > 1 -- MORE THAN ONE NETWORK

If you then need to get all records back for these mobile numbers, you would need to put the above in a subquery and join back to it:

SELECT  m.*
FROM    MobileUsers m
        INNER JOIN
        (   SELECT  MobileNo
            FROM    MobileUsers 
            GROUP BY MobileNo
            HAVING  COUNT(DISTINCT networkname) > 1 -- MORE THAN ONE NETWORK
        ) Dupe
            ON dupe.MobileNo = m.MobileNo

Problem

I am suffering from a situation where a user has changed his mobile network and subscribed our service but their mobile number is same. So, for his old subscription I have his old mobile network and for his new subscription, I have his new mobile network record. For example ``` 4474123456 subscribed gamesClub using O2 network on 2013-10-20 4474123456 subscribed musicClub using Orange network on 2013-12-05 ``` Therefore, I want to display these kind of mobile numbers who have subscribed our services using different network. ``` Database: MySQL Table : MobileUsers field: userunique,mobileno, subscription,networkname, subscribedDate ``` Query I have tried ``` SELECT mobileno,networkname,count(*) from MobileUsers group by mobileno,networkname SELECT mobileno,count(*) from MobileUsers group by mobileno,networkname ``` But none of the results are satisfactory. These also include mobileno who have multiple subscriptions having the same network. Thank You,

Original source