July 17, 2003 at 8:47 am
From the qry below, I want to get one record for each people_id with all the information from the joins. In order to achieve this I am not sure whether to use Union or temp table. For Instance, I want all the records with call type "Clinical". If use calltype=clinical in where clause, it returns no records.
Query:
select distinct p.people_id, p.gender,
e.name as ethinicname,
ep.closure_date,
ep.intake_rating_fk as Intake_Rating, ep.closure_rating_fk as Closure_Rating,
cltyp.name as call_type,
st.site_name,
ort.name as outcomeref,
rr.name as outcomerefreason,
ref.accepted as referralacceptance,
rt.name as referraltype,
rdt.name as Detailtype,
ft.name as FollowupType,
jt.name as jobtype,
jdf.name as JobDysfunction,
cr.name as Relationship,
(select distinct count(event.event_date))as NumofEvents from event
left outer join authorizations auth ON auth.authorizations_id =event.authorizations_fk
left outer join service svc ON auth.authorizations_id = svc.authorizations_fk
left outer join call cl ON cl.call_id = svc.call_fk
left outer join v_call_type cltyp ON cltyp.v_ct_id = cl.call_type_fk
left outer join episode ep ON ep.episode_id = svc.episode_fk
left outer join cases c ON c.cases_id = ep.cases_fk
left outer join v_cases_type cst ON cst.v_ct_id = c.cases_type_fk
left outer Join people p On p.people_id = c.people_fk
left outer join v_event_type et ON et.v_et_id = event.event_type_fk
left outer join v_delivery_method d ON d.v_dm_id = event.delivery_method_fk
left outer join v_contract_relation cr ON cr.v_cr_id = svc.contract_relatiON_fk
left outer join v_outcome_referral_type ort ON ort.v_ort_id = ep.outcome_referral_type_fk
left outer join v_outcome_referral_reason rr ON rr.v_orr_id = ep.outcome_referral_reason_fk
/*left outer join v_client_rating vcr ON vcr.v_cr_id = ep.intake_rating_fk
left outer join v_client_rating vclr ON vclr.v_cr_id = ep.closure_rating_fk*/
left outer join v_ethnic_group e ON p.ethnic_group_fk = e.v_eg_id
left outer join site st ON st.site_id = p.site_fk
left outer join referral ref ON ref.referral_id = svc.referral_fk
left outer join v_referral_type rt ON rt.v_rt_id = ref.referral_type_fk
left outer join v_referral_detail_type rdt ON rdt.v_rdt_id = ref.referral_detail_type_fk
left outer join contract_product_site cps ON st.site_id = cps.site_fk
left outer join contract_product cp ON cp.contract_product_id = cps.contract_product_fk
left outer join contract cc ON cc.contract_id = cp.contract_fk
left outer join event_note en ON cl.call_id = en.call_fk
left outer join v_event_note_type ent on ent.v_ent_id = en.note_type_fk
left outer join note_followup nf ON en.event_note_id = nf.event_note_fk
left outer join v_followup_type ft ON ft.v_ft_id = nf.followup_type_fk
left outer join v_job_type jt ON jt.v_jt_id = svc.job_type_fk
left outer join v_job_dysfunction jdf ON jdf.v_jd_id = svc.job_dysfunction_fk
where
st.site_name like 'pfizer%' and ep.closure_date between '04-01-2003'
and '06-30-2003' and c.status_fk='2' and p.deactivate is null and st.deactivate is null and
c.deactivate is null and svc.deactivate is null and ref.deactivate is null
and ep.deactivate is null and et.v_et_id in (6,7)
/*and cltyp.name= 'Clinical' */
group by
p.people_id, p.gender,
e.name,
ep.closure_date,
ep.intake_rating_fk, ep.closure_rating_fk,
svc.years_of_service,
cltyp.name,
st.site_name,
ort.name,
rr.name,
ref.accepted,
rt.name,
rdt.name,
ft.name,
jt.name,
jdf.name,
cr.name,
en.note_type_fk
order by st.site_name
Thanks in Advance
July 17, 2003 at 10:00 am
Since there are two questions, let me take an initial stab and you can steer me into greater understanding.
First - I would start with the v_call_type table and make sure "Clinical" exists. Backtrack by joining the call, service, authorizations and, finally, event tables until you see which link fails to produce a return. This will help you understand why no records are being returned.
Second. I don't see where you will need a union or temp table. I would personally get the COUNT( Event.event_date) as a subquery rather than grouping the whole output - but that may not be important.
Also - the LEFT JOIN seems to imply that master-detail relationships allow details without a proper master key. Seems like there could be some data integrity danger here.
Guarddata-
July 17, 2003 at 12:21 pm
I can get to Call type. The problem is, if I don't use count I get duplicate records; in One field I get call type and in another eventdate (I am doing a count on event_date). Some how I have to merge records in one field getting columns from other rows that’s what I am not sure how to do it.
Edited by - bhavnabakshi on 07/17/2003 12:22:49 PM
July 17, 2003 at 4:09 pm
Sorry - I must have closed the browser before my last response registered.
I would do two thing:
1) Start with the table that reduces the result the best. In this case I would guess that is either service or episode.
SELECT ep.closure_date...
FROM episode ep
WHERE ep.closure_date between '04-01-2003'
and '06-30-2003'
gradually add files and run the query until the results seem different than you would expect.
2) Make sure you don't connect to multiple one-to-many relationships from the same parent.
I know that is not much help. Without knowing a little more about the relationships between tables, it is hard to do more. You can use INNER JOINS if your input process assured the integrity of the data. It helps reduce the side effects.
Guarddata-
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply