CODEWITHSUNDEEP
sqlsqlite
Zerenity
Zerenity

Reputation: 107

How to select rows that have column values that are duplicates in one column but different values in the other?

Basically I want to select the rums that appear more than once but with its teachers (namn) having different telephone values (tel) ( thatd be rooms altair, kompis, and mulitplan). How do I select the rows so that it becomes;

rum   namn    tel
Altair    Jonas   1074
Altair    Lisen   1076
Kompis    Johan   1067
Kompis    Dina    1076
Multiplan Rikard  1064
Multiplan Henrik  1067

From the display below?;

tel         namn        rum
----------  ----------  ----------
1083        Agneta      ADRS
1076        Lisen       Altair
1074        Jonas       Altair
1075        Maria       Bus
1032        Amir        Cache
1063        Kjell       Gopher
1077        Kalle       Hub
1077        Magnus      Hub
1077        Urban       Hub
1054        Faramarz    Java
1076        Dina        Kompis
1067        Johan       Kompis
1071        Juha        Modem
1067        Henrik      Multiplan
1064        Rikard      Multiplan
1173        Aida        Pacman
1042        Fredrik     Printer
1076        William     RAM
1080        Janne       Sinclair
1065        Lennart     Telnet
1061        Marie       Thinktank
1073        Alan        VisiCalc

Upvotes: 2

Views: 94

Answers (3)

Kohelet
Kohelet

Reputation: 404

select x.*
into #df
from
 (
select 1083 tel, 'Agneta' namn, 'ADRS' rum UNION ALL 
select 1076 tel, 'Lisen' namn, 'Altair' rum UNION ALL 
select 1074 tel, 'Jonas' namn, 'Altair' rum UNION ALL 
select 1075 tel, 'Maria' namn, 'Bus' rum UNION ALL 
select 1032 tel, 'Amir' namn, 'Cache' rum UNION ALL 
select 1063 tel, 'Kjell' namn, 'Gopher' rum UNION ALL 
select 1077 tel, 'Kalle' namn, 'Hub' rum UNION ALL 
select 1077 tel, 'Magnus' namn, 'Hub' rum UNION ALL 
select 1077 tel, 'Urban' namn, 'Hub' rum UNION ALL 
select 1054 tel, 'Faramarz' namn, 'Java' rum UNION ALL 
select 1076 tel, 'Dina' namn, 'Kompis' rum UNION ALL 
select 1067 tel, 'Johan' namn, 'Kompis' rum UNION ALL 
select 1071 tel, 'Juha' namn, 'Modem' rum UNION ALL 
select 1067 tel, 'Henrik' namn, 'Multiplan' rum UNION ALL 
select 1064 tel, 'Rikard' namn, 'Multiplan' rum UNION ALL 
select 1173 tel, 'Aida' namn, 'Pacman' rum UNION ALL 
select 1042 tel, 'Fredrik' namn, 'Printer' rum UNION ALL 
select 1076 tel, 'William' namn, 'RAM' rum UNION ALL 
select 1080 tel, 'Janne' namn, 'Sinclair' rum UNION ALL 
select 1065 tel, 'Lennart' namn, 'Telnet' rum UNION ALL 
select 1061 tel, 'Marie' namn, 'Thinktank' rum UNION ALL 
select 1073 tel, 'Alan' namn, 'VisiCalc' rum
) x;

select rum, namn, tel 
from #df
where rum in  
    (
    select rum 
    from #df
    group by rum
    having count(distinct tel)>1 -- (number of different tel numbers for specific rum)>1
    )

enter image description here

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

For each rum group, compare the count of records having the same rum against the distinct count of telephone numbers:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT rum
    FROM yourTable
    GROUP BY rum
    HAVING COUNT(*) > 1 AND COUNT(DISTINCT tel) > 1
) t2
    ON t1.rum = t2.rum;

The acceptance criteria is that a rum group has two or more records, while at the same time there are at least two different phone numbers for that group.

Demo

Upvotes: 1

GMB
GMB

Reputation: 222402

You can use exists to filter the table for records where another record exists with the same rum and a different tel:

select t.*
from mytable t
where exists (
    select 1
    from mytable t1
    where t1.rum = t.rum and t1.tel <> t.tel
)
order by rum, tel

Demo on DB Fiddle:

 tel | namn   | rum      
---: | :----- | :--------
1074 | Jonas  | Altair   
1076 | Lisen  | Altair   
1067 | Johan  | Kompis   
1076 | Dina   | Kompis   
1064 | Rikard | Multiplan
1067 | Henrik | Multiplan

Upvotes: 2

Related Questions