CODEWITHSUNDEEP
sqloracle-databaseselectreferencesubquery
Politiepet
Politiepet

Reputation: 93

references in select statements

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

Answers (4)

David Faber
David Faber

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

Pரதீப்
Pரதீப்

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

Asanga Ranasinghe
Asanga Ranasinghe

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

rickyalbert
rickyalbert

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

Related Questions