CODEWITHSUNDEEP
sqloracle-databaseself-join
Remya
Remya

Reputation: 21

Self Join in PLSQL

I have a table, P, with a personid column and licensetypeid column.

Each person can have multiple license types, adding additional rows to the table for that person ID.

I need to find the rows where personid has both licenseid of 1 and of 5.

I can't write:

SELECT personid, licensetypeid 
FROM P 
WHERE licensetypeid=1 AND licensetypeid=5;

I heard I should use a self-join to do this. How do I do a self join to solve this problem?

Upvotes: 0

Views: 1322

Answers (5)

Erwin Smout
Erwin Smout

Reputation: 18408

If your set of required license types is "fixed", or at least if the cardinality of the set of required license types is fixed, then the given answers will work OK.

Otherwise, you need to write the SQL equivalent of what is known as "relational division".

That goes as follows:

(1) Compute the set of persons that LACK at least one of the required license types :

SELECT personid 
from P 
WHERE EXISTS
  (
    SELECT licenseid 
    from NEEDEDLICENSETYPE AS NLT 
    WHERE NOT EXISTS (
      SELECT * 
      FROM P AS PBIS 
      WHERE 
        PBIS.personid = P.personid AND
        PBIS.licensetype = NLT.licensetype
    )
  )

NEEDELICENSETYPE represents whatever SQL statement you need to compute the set of required license types on a particular invocation.

(2) Select the data for the persons with a number that does not appear in (1) :

SELECT ... FROM P WHERE personid NOT IN (...)

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48121

Self-joins and other techniques of accessing the table more than once will work, but may reduce performance and are unwieldy if you need to generalize to larger sets of ids.

You can do it with a single reference to the table by counting the number of matching rows per person:

select personid from P
  where licensetypeid in ('1','5')
  group by personid
  having count(*) = 2

This can easily be expanded if you want to require a larger licensetypeid values:

select personid from P
  where licensetypeid in ('1','5','7')
  group by personid
  having count(*) = 3

(in the self-join version you would have to add an additional join for each additional value)

Or if you want to find people that have at least 2 types out of a larger set of types:

select personid from P
  where licensetypeid in ('1','5', '7', '10')
  group by personid
  having count(*) >= 2

Now, unlike your sample query, the licensetypeid is not included in the result set. If that is necessary for some reason, you can do a simple trick in the case of 2 values:

select personid, min(licensetypeid) licensetype1, max(licensetypeid) licensetype2
  from P
  where licensetypeid in ('1','5')
  group by personid
  having count(*) = 2

But a more general approach is to group the values into a simple collection:

select personid, collect(licensetypeid) licensetypeidlist
  from P
  where licensetypeid in ('1','5')
  group by personid
  having count(*) = 2

Upvotes: 4

Nils Weinander
Nils Weinander

Reputation: 2141

select personid, licensetypeid
from P P1
where exists (
   select 1
   from P P2
   where P2.personid = P1.personid
      and P2.licensetypeid = 1
) and exists (
   select 1
   from P P2
   where P2.personid = P1.personid
      and P2.licensetypeid = 5
)

Upvotes: 2

schurik
schurik

Reputation: 7928

SELECT distinct
       p1.personid
      ,p1.licensetypeid 
      ,p2.licensetypeid
from P p1, P p2
WHERE p1.personid = p2.personid
AND   p1.licensetypeid = 1
AND   p2.licensetypeid = 5
;

Upvotes: 1

ajm
ajm

Reputation: 13213

Try this 

select personid,licensetypeid from P where licensetypeid in ('1','5')

Upvotes: 0

Related Questions