Inner and Outer Join Combo

[ryanrat]

I've not too terribly skilled at SQL, and I'm having problems with this join.

The current query is as follows:

SELECT
EM.*, Activity.*, CL.*, Contacts.*, Opportunity.*
FROM
EM, Activity, CL, Contacts, Opportunity
WHERE
EM.Employee = Activity.Employee AND CL.ClientID = Activity.ClientID AND Contacts.ContactID = Activity.ContactID AND CL.ClientID = Contacts.ClientID AND Opportunity.OpportunityID = Activity.OpportunityID AND CL.ClientID = Opportunity.ClientID AND Contacts.ContactID = Opportunity.ContactID

This query works great, except that I need the query to produce ALL Activities, not just the ones that have Opportunities associated with them. I understand that an Outer Join is the way to do this, and I think that I need the relationship between
Opportunity.OpportunityID = Activity.OpportunityID

to be exactly that to produce what I'm looking for. I'm not sure how I should structure the query to produce this result.

Any suggestions?

Thanks!

 

[swampBoogie]

SELECT 
EM.*, Activity.*, CL.*, Contacts.*, Opportunity.*
FROM EM inner join Activity
on EM.Employee = Activity.Employee
inner join CL
on CL.ClientID = Activity.ClientID 
inner join  Contacts
on Contacts.ContactID = Activity.ContactID
and CL.ClientID = Contacts.ClientID
left outer join Opportunity
on Opportunity.OpportunityID = Activity.OpportunityID 
and CL.ClientID = Opportunity.ClientID 
and Contacts.ContactID = Opportunity.ContactID