Relationship Question

[Hashiba14]

I currently pull reports off a sql server on crystal 8.5 that gives me all the information for a patient.

My only problem is if the patient does not have a carrier, it does not return that patient, but I need it to.

If I change the relationship of the carrier table to left outer, I get the same information. If I do a right outer join, the report fails. Can someone please help me.

Thank you

Attached is the current sql code:

SELECT
appt.appt_id, appt.start_datetime, appt.long_comment, appt.apptstatus_id, appt.resunit_id,
resunit.resunit_id, resunit.abbr,
probooking.act_name,
resbooking.start_datetime,
physician.name,
pat.pat_id, pat.birthdate, pat.home_phone, pat.work_phone, pat.name_display,
visit.admit_reason, visit.current_meds,
resource.abbr, resource.restype_id,
loc2.name,
carrier.name
FROM
prod.dbo.appt appt,
prod.dbo.visitapptlist visitapptlist,
prod.dbo.resunit resunit,
prod.dbo.probooking probooking,
prod.dbo.resbooking resbooking,
prod.dbo.res physician,
prod.dbo.patbooking patbooking,
prod.dbo.pat pat,
prod.dbo.visit visit,
prod.dbo.res resource,
prod.dbo.loc loc2,
prod.dbo.visitpayerlist visitpayerlist,
prod.dbo.payer payer,
prod.dbo.carrier carrier
WHERE
appt.appt_id = visitapptlist.appt_id AND
appt.resunit_id = resunit.resunit_id AND
appt.appt_id = probooking.appt_id AND
appt.appt_id = resbooking.appt_id AND
appt.attending_phys_id *= physician.res_id AND
appt.appt_id = patbooking.appt_id AND
patbooking.pat_id = pat.pat_id AND
visitapptlist.visit_id = visit.visit_id AND
resbooking.res_id = resource.res_id AND
resunit.resunit_id = loc2.loc_id AND
visit.visit_id = visitpayerlist.visit_id AND
visitpayerlist.payer_id = payer.payer_id AND
payer.carrier_id = carrier.carrier_id AND
appt.start_datetime >= "Jun 1 2007 00:00:00AM" AND
appt.start_datetime <= "Jun 1 2007 11:59:59PM" AND
(appt.apptstatus_id = 3 OR
appt.apptstatus_id = 1) AND
resource.restype_id = 2 AND
resbooking.start_datetime >= "Jun 1 2007 00:00:00AM" AND
resbooking.start_datetime <= "Jun 1 2007 11:59:59PM" AND
resunit.resunit_id = 32 AND
pat.pat_id = 2483865
ORDER BY
appt.resunit_id ASC

 

[Hashiba14]

If I take out the carrier field from ther report, I get all the records I need, but if I put it in the query, it will only return those that do have a carrier.

 

[lbass]

You need a left outer join FROM the payer table TO the carrier table. I'm not sure how you indicate that with your current syntax though.

-LB

 

[Hashiba14]

If I make the left outer join from the payor to carrier table, this is the syntax:

payer.carrier_id *= carrier.carrier_id.

Unfortunately, when I make this change, I still don't get all the records. Hope this helps out.

 

[lbass]

You are linking to multiple tables before the link to the carrier, so I would try adding left outer joins all along that sequence and observe what happens:

appt->visapptlist->visit->visitpayerlist->payer->carrier

You could start by changing the links from the right hand end of the sequence until you see all records returned.

-LB

 

[synapsevampire]

You'd need to LO Join everything from Appt to Carrier.

I suggest that you switch to real SQL anyway.

Copy and paste your SQL into a New View in Enterprise Manager.

It will convert it for you, plus you can test and tweak in there.

-k