CODEWITHSUNDEEP
sql
jaji18
jaji18

Reputation: 55

How to select rows that have more than 1 distinct values in SQL?

I have 4 tables:

**[PERSON]

ID_Person   Person_NAME**
    1   First name 1 Last name 1
    2   First name 2 Last name 2
    3   First name 3 Last name 3
    4   First name 4 Last name 4
    5   First name 5 Last name 5
    6   First name 6 Last name 6

**[mobile_number]
ID  Mobile_Number   OPERATOR**
1   797900010   M
2   797900011   M
3   698900010   I
4   797900012   I
5   698900011   J
6   797900013   T
7   797900011   J
8   698900012   I
9   797900014   L
10  698900013   M
11  797900015   M


**[user_mobile]
ID  ID_USER ID_MOBILE**
1   1   1
2   1   3
3   1   11
4   2   6
5   2   8
6   3   5
7   3   10
8   4   2
9   5   4
10  5   7
11  6   9

**[MESSAGE_ID]
ID_Message  ID_MOBILE   Message Date**
1   1   text 1          12/04/2011
2   1   text 2          07/07/2011
3   1   text 3          05/11/2011
4   2   text 4          01/13/2012
5   2   text 5          17/02/2012
6   2   text 6          13/12/2012
7   3   text 7          25/12/2011
8   4   text 8          11/11/2012
9   4   text 9          03/03/2012
10  5   text 10         30/04/2012
11  5   text 11         28/02/2012
12  6   text 12         01/06/2011
13  7   text 13         19/08/2010
14  8   text 14         22/12/2010
15  8   text 15         14/05/2010
16  9   text 16         09/04/2012
17  10  text 17         11/05/2011
18  11  text 18         15/01/2012

I have to select all users that have sent sms in different years Columns to be displayed: Person Name, Mobile Number, SMSText, Year

My solution:

    Select a.Person_Name, Mobile_Nr, message, year(Date) as Years from PERSON a
    inner join USER_MOBILE b on a.ID_PERSON=b.ID_USER
    inner join MOBILE_NUMBER c on b.ID_MOBILE=C.ID
    inner join MESSAGE_ID d on c.ID=d.ID_Mobile
    group by Person_Name, Mobile_Nr,message,year(Date)
    having count(distinct year(date))>1

But it does not return nothing. if i change it to "having count(distinct year(date))=1" i get :

     **Person_Name  Mobile_Nr     message   Years**
Firstname 1 Lastname 1  698900010   text7   2011
Firstname 1 Lastname 1  797900010   text1   2011
Firstname 1 Lastname 1  797900010   text2   2011
Firstname 1 Lastname 1  797900010   text3   2011
Firstname 1 Lastname 1  797900015   text18  2012
Firstname 2 Lastname 2  698900012   text14  2010
Firstname 2 Lastname 2  698900012   text15  2010
Firstname 2 Lastname 2  797900013   text12  2011
Firstname 3 Lastname 3  698900011   text10  2012
Firstname 3 Lastname 3  698900011   text11  2012
Firstname 3 Lastname 3  698900013   text17  2011
Firstname 4 Lastname 4  797900011   text4   2012
Firstname 4 Lastname 4  797900011   text5   2012
Firstname 4 Lastname 4  797900011   text6   2012
Firstname 5 Lastname 5  698900009   text13  2010
Firstname 5 Lastname 5  797900012   text8   2012
Firstname 5 Lastname 5  797900012   text9   2012
Firstname 6 Lastname 6  797900014   text16  2012

But this is wrong, i want to display only the users that sent sms in different years.

Upvotes: 2

Views: 688

Answers (3)

Philip Kelley
Philip Kelley

Reputation: 40289

Try this -- same base query, plus a WHERE-clause subquery that filters out Persons who did not have calls from multiple years.

--  Person, phone number, message, and year, for persons who had messages in multiple years
Select a.Person_Name, Mobile_Nr, message, year(Date) as Years
 from PERSON a
 inner join USER_MOBILE b on a.ID_PERSON=b.ID_USER
 inner join MOBILE_NUMBER c on b.ID_MOBILE=C.ID
 inner join MESSAGE_ID d on c.ID=d.ID_Mobile
 where a.ID_PERSON in (select a.ID_PERSON
                        from PERSON a
                         inner join USER_MOBILE b on a.ID_PERSON=b.ID_USER
                         inner join MOBILE_NUMBER c on b.ID_MOBILE=C.ID
                         inner join MESSAGE_ID d on c.ID=d.ID_Mobile
                        group by a.ID_PERSON
                        having count(distinct year(d.date)) > 1)

(I couldn't check the syntax, but the concept is sound)

Upvotes: 1

Kaspars Ozols
Kaspars Ozols

Reputation: 7017

Try something like this:

Select a.Person_Name, Mobile_Nr, message, year(D.Date) as Years from PERSON a
    inner join USER_MOBILE b on a.ID_PERSON=b.ID_USER
    inner join MOBILE_NUMBER c on b.ID_MOBILE=C.ID
    inner join MESSAGE_ID d on c.ID=d.ID_Mobile
WHERE 

EXISTS(

    SELECT 1 
    FROM 
       PERSON a1
       INNER JOIN USER_MOBILE b1 on a1.ID_PERSON=b1.ID_USER
       inner join MOBILE_NUMBER c1 on b1.ID_MOBILE=C1.ID
       inner join MESSAGE_ID d1 on c1.ID=d1.ID_Mobile
    WHERE 
       C1.MOBILE_NUMBER = C.MOBILE_NUMBER AND 
       year(D1.Date)<>year(D.DATE) 

)

I know it could be simplified a lot, but I just wanted to demonstrate the concept here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Your solution is quite close. The problem is that you are aggregating by year(date), so only one year can appear on any given row. Try this:

   Select a.Person_Name, Mobile_Nr, message,
          count(distinct year(Date)) as Years
   from PERSON a inner join
        USER_MOBILE b
        on a.ID_PERSON = b.ID_USER inner join
        MOBILE_NUMBER c
        on b.ID_MOBILE = C.ID inner join
        MESSAGE_ID d
        on c.ID = d.ID_Mobile
    group by Person_Name, Mobile_Nr, message
    having count(distinct year(date)) > 1;

Upvotes: 0

Related Questions