JOIN query

[2122002]

I have 3 tables: Person, Patient and Donor.

Person Table contains:

Pno | Name | Sex | ABO
----------------------------------
101 | David | Male | AB
102 | Williams | Female | O
113 | Smithy | Male | A
112 | Cinthia | Female | A


Patient Table contains:
Pno | GPName | GP_Addrs
------------------------------
102 | Dr. Terry | Route 66 stree, N.Y
113 | Dr. Terry | Burger Stree, N.Y


Donor Table contains:

Pno | Dondate
-------------------
112 | 03/05/2004
101 | 04/04/2005

I want a query to give for each patient, Patient Pno together with the Pno of all Donor who have the same ABOgroup. (NOTE: that both patient and donor datas/records are updated/record in PERSON TABLE)

How do I go about this?. Please help me.

Thanks

 

[djbjr]

Im tired so maybe its me, but I dont understand exactly what you are asking.

Can you give me a sample of what your result set would look like?

 

[Dagon]

I haven't tested this, but I think you'd want something like:

select p1.pno as patient, p1.name as patient_name,
p2.pno as donor, p2.name as donor_name
from person p1, donor d, person p2
where p1.abo = p2.abo
and d.pno = p2.dno
order by p1.pno, p2.pno

It might give a very large result set though, since you are joining on a not very selective key (the blood group).

 

[Dagon]

Failed to take into account that a person wouldn't want to give blood to themselves (presumably);

select p1.pno as patient, p1.name as patient_name,
p2.pno as donor, p2.name as donor_name
from person p1, donor d, person p2
where p1.abo = p2.abo
and d.pno = p2.pno
and p1.rowid <> p2.rowid
order by p1.pno, p2.pno
/


This gives:

PATIENT PATIENT_NA DONOR DONOR_NAME
--------- ---------- ---------- ----------
113 Smithy 112 Cinthia

Is that what you would expect ?

 

[2122002]

Hi Dogan and bjdjr for your help:

Dogan, its error free but generated lots of result as you've said.

The simple result should look like this (i.e the result of the ABO they have in common which is "A":

PNO_PATIENT | PNO_DONOR | ABO
================================
113 | 112 | A


OR (another format)

PNO_PATIENT | ABO
--------------------
113 | A


PNO_DONOR | ABO
-------------------
113 | A

 

[SantaMufasa]

It might give a very large result set.

I agree totally...Let's presume that you have 12,000 rows that are evenly divided amongst the four blood type combinations. (Unrealistic, I know, but mathematically simpler to discuss.) if you print out those 12,000 rows, then the output represents 36 million rows of output. If all you want to see is the people that share the same blood types, why not just print out a list of "A", "B", "O", and "AB" patients? You would be printing only 12K rows instead of 36 million.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[2122002]

Dagon, still work with duplicate result, can we just eliminate NAME to appear and replace it with ABO?.

Thanx for looking into this again.

 

[SantaMufasa]

...can we just eliminate NAME to appear and replace it with ABO?.

Unless I am missing that point of what you are saying, what the h*** good is a report that prints out:
[tt]
PNO_DONOR | ABO
-------------------
113 | A
| A
| A
| A
| A
| A
| A
| A
| A[/tt]
...et cetera?

Can you please just post a sample of what output you want to see?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[2122002]

to generate Result from persons:

PNO | ABO


where Patient and Donor ABO group are equal. please note that both Patient and Donor PNO records are stored in PERSON table.

PNO field_name in Patient and Donor table are Primary Key REFERENCE Person.

 

[Dagon]

Would a "select distinct" be better ?

select distinct p1.pno as patient,
       p2.abo
from person p1,
(select p.pno, p.abo from
donor d, person p
where d.pno = p.pno) p2
where p1.abo = p2.abo (+)
order by p1.pno, p2.abo

 

[SantaMufasa]

It's difficult to tell, Dagon, since we still do not have a definitive sample output that contains sample data (from 2122002).

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[2122002]

Thank you Dagon and Mufasa for your follow up. Dagon, this query works but I removed (+)..... I think "+" is outer join in Oracle.

One more questions:

Will it be possible to have the output display in this formati.e to list the PNO of the patient, next to it will be PNO of donor and thier ABO (where ABO matches)

Pno(Patient) | Pno(Donor) | ABO
-----------------------------------
113 | 112 | A

I really appreciate your help.

regards.

 

[Dagon]

That gets you back to the query I did earlier, which was returning masses of data. We seem to be going in circles !

select p1.pno as patient,
       p2.pno as donor,
       p2.abo
from person p1,
(select p.pno, p.abo from
donor d, person p
where d.pno = p.pno) p2
where p1.abo = p2.abo
order by p1.pno, p2.pno

 

[SantaMufasa]

212...

Can you tell us the business need/information that you are trying to display. For example:

"We need to display the names of all donors that share the same blood type."

or

"We need to display the name of a single potential blood donor whose blood type matches the blood type of a specific patient."

or

"We need to display for a specific patient the total count of potential donors with the same blood type plus the count of all potential donors that are Universal Donors."

et cetera.

Once we clearly understand your business need, perhaps we can offer specific solutions to resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[2122002]

Dear Mufasa:

business needs:

"We need to display the NAMES and NUMBERS of all PATIENT that share the same blood type with DONOR." i.e where PATIENT blood matches with the blood of donor.


Thank you.

regards.

 

[2122002]

Person Table contains:
Pno | Name | Sex | ABO
----------------------------------
101 | David | Male | AB
102 | Williams | Female | O
113 | Smithy | Male | A
112 | Cinthia | Female | C


Patient Table contains:
Pno | GPName | GP_Addrs
------------------------------
102 | Dr. Terry | Route 66 stree, N.Y
113 | Dr. Terry | Burger Stree, N.Y


Donor Table contains:
Pno | Dondate
-------------------
112 | 03/05/2004
101 | 04/04/2005

I also want report on:

1) Any "AB" Patient can receive blood from any Donor"

2) Any "O" patient can recive from any "C" Donor"

Thank you all for looking into this again.