View Single Post
  #1 (permalink)  
Old March 20th, 2009, 03:05 PM
jcarswelljr jcarswelljr is offline
Registered User
Join Date: Dec 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default retrieving all records

i want to retrieve all records regardless if they were associated with an office or had person_id in the other 7 tables what do i need to add to this query to achieve this? presently the query retrieves 1200 records that have person_id in, EPPSGlobalProd.dbo.person and any table in the case statement. there are 31000 records altogether.
thank you for all your help!

SELECT DISTINCT p.person_id,


p.first_name, as office_name, g.BU,
count(c.clean_cut_experience_id) > 0
then 'X'
end clean_cut_experience,
when count(r.cri_experience_id) > 0
then 'X'
end cri_experience,
count(d. desorption_tech_experience_id) > 0
then 'X'
end desorption_tech_experience,
count(w.dwm_wellsite_experience_id) >0
then 'X'
end dwm_wellsite_experience,
when count( f.fluids_experience_id) > 0
then 'X'
end fluids_experience,
when count(pressure_control_equipment_experience_id) > 0
then 'X'
end pressure_control_equipment_experience,
when count(rdf_experience_id) > 0
then 'X'
end rdf_experience,

when count(speedwell_experience_id) > 0
then 'X'
end speedwell_experience,

when count(completion_procedures_experience_id) > 0
then 'X'
end completion_procedures_experience

FROM global_business_unit g
join o on g.office_name =
join LogITProd.dbo.security_user_office_assignment u on u.office_id = o.office_id
join EPPSGlobalProd.dbo.person p on p.ad_guid = u.ad_guid
left outer JOIN clean_cut_experience c ON p.person_id = c.person_id

left outer JOIN cri_experience r ON p.person_id = r.person_id

left outer JOIN desorption_tech_experience d ON p.person_id = d.person_id

left outer JOIN dwm_wellsite_experience w ON p.person_id = w.person_id

left outer JOIN fluids_experience f ON p.person_id = f.person_id

left outer JOIN pressure_control_equipment_experience s ON p.person_id = s.person_id

left outer JOIN rdf_experience x ON p.person_id = x.person_id

left outer JOIN speedwell_experience n ON p.person_id = n.person_id

left outer JOIN completion_procedures_experience e ON p.person_id = e.person_id
--where ='Aberdeen'
group by p.person_id, p.last_name, p.first_name,, g.BU

HAVING Count(c.clean_cut_experience_id)
+ Count(r.cri_experience_id)
+ Count(d.desorption_tech_experience_id)
+ Count(w.dwm_wellsite_experience_id)
+ Count(f.fluids_experience_id)
+ Count(pressure_control_equipment_experience_id)
+ Count(rdf_experience_id)
+ Count(speedwell_experience_id)
+ Count(e.completion_procedures_experience_id)
order by