Reputation: 93
I'm using Oracle SQL Developer
I have a patient table with fields name and phonenumber. I want to only display the names of people who have the same number as at least someone else.
So the patient table is like this:
create table PATIENT(
name varchar2(9) primary key,
phoneNumber varchar2(15) null
);
So I tried the folowing:
select name, phoneNumber
from PATIENT
where phoneNumber in(select phoneNumber
from PATIENT
where name not .....);
So where the dots are there should be a reference to name in the parent select statement. Is there anyway to do this, or is there a totaly different way to solve this?
Upvotes: 0
Views: 54
Reputation: 12495
No one likes to use GROUP BY or analytic functions?
SELECT name, phonenumber
FROM patient
WHERE phonenumber IN ( SELECT phonenumber FROM patient
GROUP BY phonenumber
HAVING COUNT(*) > 1 )
or
SELECT name, phonenumber FROM (
SELECT name, phonenumber, COUNT(*) OVER ( PARTITION BY phonenumber ) AS patient_cnt
FROM patient
) WHERE patient_cnt > 1
Upvotes: 0
Reputation: 93754
Self join should work for you.
SELECT a.NAME,
a.phoneNumber
FROM PATIENT a
JOIN PATIENT b
ON a.phoneNumber = b.phoneNumber
AND a.NAME <> b.NAME
Upvotes: 1
Reputation: 92
I think this will give you the expected answer.
select parent_q.NAME,
parent_q.phoneNumber
from PATIENT parent_q
where (select count(sub_q.phoneNumber)
from PATIENT sub_q
where sub_q.phoneNumber = parent_q.phoneNumber) > 1
Upvotes: 1
Reputation: 2652
I think you need a self-join:
SELECT *
FROM PATIENT P1 JOIN PATIENT P2 ON (P1.phoneNumber = P2.phoneNumber)
The result will be something like:
'aaa' '12345' 'bbb' '12345'
'bbb' '12345' 'aaa' '12345'
'ccc' '00000' 'ddd' '00000'
'ddd' '00000' 'ccc' '00000'
As you can see, there will be "duplicated rows", because for symmetrical property, if A has the same number of B, B has the same number of A.
Upvotes: 0