Query question

[tang]

Hi everyone,

I need suggestions or idea. please help.

I have three tables.

patient (patientid, firstname, lastname, dob,
street, city, state, phone)

insuranceinfo(patientid,insuranceid,groupname,grouno,insurername,insurerid)

insurance(insuranceid,insurancename,street,city,state,zip,phone)
insurance table is a reference table.
Patientid, insuranceid are the primary key for the
insuranceinfo table.

If patient A has insurance, the insuranceinfo table
would have an entry for
A.

If patient B doesn’t have insurance then in the
insuranceinfo table would
not have anything.


My question is how can I get all the patient’s
information from these tables
in one query?
It doesn’t matter if the patient does or does not
have insurance.



Thanks in advance!

[Mnemonical]

maybe...

SELECT P.Patientid, P.firstname, P.lastname, P.dob, P.street, P.city,
P.state, P.phone,i.insuranceid, i.groupname, i.groupno, i.insurname,
i.insurerid
FROM Patient P, insuranceinfo i
WHERE p.patientid = i.patientid;


??


F.

[sanjaysn]

hi ,
i go through your problem .
and as per u'r requirement i make one query hope it will helpfull for you .

SELECT P.Patientid, P.firstname, P.lastname, P.dob, P.street, P.city,
P.state, P.phone,i.insuranceid, i.groupname, i.groupno, i.insurname,
i.insurerid
FROM Patient P, insuranceinfo i
WHERE ( p.patientid = i.patientid
or p.patientid not in (select patientid from insuranceinfo));


hope it will solve your problem .

sanju

[wjh_uk]

Use an outer join so you include the insurance details where they have insurance and none where they don't

[keshavsahu]

try outer join....or inner join
SELECT P.Patientid, P.firstname, P.lastname, P.dob, P.street, P.city,
P.state, P.phone,i.insuranceid, i.groupname, i.groupno, i.insurname,
i.insurerid
FROM Patient P, insuranceinfo i
WHERE p.patientid(+) = i.patientid;

[tang]

Thanks a lot to all of you. Because one table can only outer join with maximum one of the other table. The information I need actually required outer join two tables -- patient table with insuranceinfo table then insuranceinfo with insurance. It will never work. Since I am in the design phrase of the database development, all I am going to do is to add an entry in the insurance table for those people who don't have insurance. This will solve the problem. Thanks again.

[chrisrlong]

Whoa!

Unless I am missing something, you don't have a problem, except that maybe your outer-joins are backwards.

Please supply the statement that is giving you the error and we'll see what we can do.

Adding an entry to a table when there should not be one should be an absolute desperation measure. I find it hard to believe it is necessary here.

- Chris

[tang]

Hello Chris,

The following are the queries that I wrote:

Query1

select patient.patientid,firstname from insuranceinfo,patient where patient.patientid=insuranceinfo.patientid(+);

This query will work if I don't need any information from the insurance table such as insurancename, insuranceaddress. It is not what I want.

Query2

select patient.patientid,firstname,insurancename from insurance,insuranceinfo,patient where pat
ient.patientid=insuranceinfo.patientid(+) and insurance.insuranceid=insuranceinfo.insuranceid;

This query will always bring me back the patient who have insurance.


Any suggestions? Thanks.

[ales]

Originally posted by tang
The information I need actually required outer join two tables -- patient table with insuranceinfo table then insuranceinfo with insurance. It will never work.

It will work. You can outer-join a table to already outer-joined table:

select patient.patientid, firstname, insurancename
from insurance,insuranceinfo,patient
where patient.patientid=insuranceinfo.patientid(+)
and insurance.insuranceid(+)=insuranceinfo.insuranceid;

This is the case that will never work, because insuranceinfo is outer-joined to two tables:

select patient.patientid, firstname, insurancename
from insurance,insuranceinfo,patient
where patient.patientid=insuranceinfo.patientid(+)
and insurance.insuranceid=insuranceinfo.insuranceid(+);

Ales

[tang]

It works!
Thank you so much everyone. It is a great feeling knowing that someone out there who truly want to help the others.