outer joins

Hey, i'm not the best SQL writer there ever was so I thought I would run this by someone who might know a fair bit more than myself

i've got a query that is supposed to be returning all the contacts in my database along with their group and their group manager, the query i have right now is something along these lines.

SELECT
c1.contact_id, c1.group_id, c1.email, c2.contact_id, c2.email
FROM
contacts c1, contacts c2, memberships
WHERE
c1.group_id = memberships.group_id AND
c2.contact_id = memberships.contact_id AND
memberships.managerflag = 1

-- memberships is a table holding contact_id's and their group + whether or not they are a manager of that group.. contact_id + group_id share the role of the primary key
-- contacts is a table holding general contact information, their primary group, their name, id, e-mail etc.. contact_id is the primary key


the problem with this query is that some groups don't have managers, so any of the contacts in those groups aren't returned, what i want is them returned with null values in the manager fields

so i'm thinking i can just do an outer join to fix this problem? but i don't have much experience with them so i thought i would see if someone could tell me if i've got the right idea before i attempt to do this in a much more elaborate query than the one below..

SELECT
c1.contact_id, c1.group_id, c1.email, c2.contact_id, c2.email
FROM
contacts c1, memberships LEFT OUTER JOIN contacts c2
ON
c2.contact_id = memberships.contact_id AND
memberships.managerflag = 1
WHERE
c1.group_id = memberships.group_id

this just doesn't seem right to me, that's why I'm here

 

Alright, I'm thinking we may be better off if I go ahead and put the entire query in here.... this is how it looks at the moment, keep in mind I didn't design this database and it could have been made a lot better than it is..

SELECT DISTINCT
contactinfo.contact_id as 'Contact ID',
(ContactInfo.last_name + ', ' +
ContactInfo.first_name) as 'Name',
groups.name as 'Group Name', groups.group_id,
ContactPhone.phone as 'All Phones',
ContactPhone.phone_ext as 'Ext',
case ContactPhone.location when 0 then 'Home' when 1 then 'Office' when 2 then 'Mobile' end as 'Location',
ContactMobilePhone.phone as 'Mobile Phones',
ContactInfo.e_mail as 'E-mail',
managerinfo.contact_id as 'Manager ID',
(ManagerInfo.last_name + ', ' +
ManagerInfo.first_name) as 'Managers Name',
ManagersPhone.phone as 'Managers Phone',
ManagersPhone.phone_ext as 'Ext.',
case ManagersPhone.location when 0 then 'Home' when 1 then 'Office' when 2 then 'Mobile' end as 'Location.',
ManagerInfo.e_mail as 'Managers E-mail'
FROM
contacts as ContactInfo,
groups,
phone as ContactPhone,
contacts as ManagerInfo,
phone as ContactMobilePhone,
memberships,
phone as ManagersPhone
WHERE
(groups.Group_ID = ContactInfo.group_id) -- contact's group
AND (ContactPhone.contact_id = ContactInfo.contact_id) -- phone id = contact id
AND (ContactMobilePhone.contact_id = ContactInfo.contact_id) -- mobile phone id = contact id
AND (ContactPhone.phone_use <> 'email') -- Filter out emails
AND (ContactMobilePhone.phone_use <> 'email')
AND (ManagersPhone.phone_use <> 'email')
AND (ContactMobilePhone.Location = 2) -- mobile phone
AND ((ContactMobilePhone.supplier_id = 5388) or (ContactMobilePhone.supplier_id = 8747)) -- mobile phone supplier = office phone supplier
AND (memberships.group_id = ContactInfo.group_id) -- manager and contact are in same group
AND (memberships.contact_id = ManagerInfo.contact_id)-- managers contact_id = managers membership id
AND (memberships.admin_flag = 1) -- manager is a manager
AND (ManagersPhone.contact_id = ManagerInfo.contact_id) -- managers contact_id = managers phone contact_id

so there that is..
phone is a list of phone numbers(phone.phone) along with their suppliers(supplier_id), users(contact_id), location(home, office, mobile) etc..

contacts contain individual contact information for contacts, their primary group, e-mail, id and so on..

memberships is a list containing one row for every group each contact is a member of and whether or not that contact is a manager (admin_flag)

groups is a list of every group and their attributes like description, name etc.

so what i'm trying to do is for every contact that has a mobile phone from the supplier #5388 or #8747 i want to display their name, id, e-mail, each different mobile phone they have from one of those suppliers, any other phones they have along with their location, and than the tricky part, is to get them paired up with their managers and each of their managers phones.. but since some groups don't have managers this is where i lose some of the required contacts from the result set.....

i hope i have explained this clearly enough for someone to take a stab at it.. i've been working on this query on and off for a couple days so i'd like to get it done with.

thanks

 

could you post your database structure please. It's really hard to see what exactly you need based on the enormous query you posted above

 

Here are the tables used

Contacts - PK Contact_ID, FK Group_ID -> Groups.Group_ID, first_name, last_name, e_mail...

Groups - PK Group_ID, name, description...

Phone - PK Phone_ID, FK Contact_ID -> Contacts.Contact_ID, Location(home, mobile, office), FK Supplier_ID -> Groups.Group_ID, phone (phone number)

Memberships - PK Group_ID + Contact_ID, admin_flag (manager or not)


I'm hoping this is something like what you were looking for

Thanks for giving the problem a look at least

 

I've finally figured out a way to get it done, however it does give a cross product of the different numbers (since many contacts have multiple office numbers), but that's ok with me for now.. thought i'd put the query up just incase anyone was interested

SELECT 	DISTINCT
	ContactInfo.name as 'Client Name',
	ContactInfo.groupname as 'Group Name',
	ContactPhone.office as 'Office Phone',
	ContactInfo.mobilephone as 'Mobile Phone',
	ContactInfo.email as 'E-mail',
	ManagerInfo.name as 'Managers Name',
	ManagerPhone.office as 'Office Phone',
	ManagerInfo.email as 'E-mail'
FROM	(SELECT phone.phone as 'Office',
		phone.contact_id
	 FROM	phone
	 WHERE 	phone.location = 1
		AND phone.phone_use <> 'email') as ContactPhone
	RIGHT OUTER JOIN
	(SELECT (contacts.first_name +' '+ contacts.last_name) as 'Name',
		groups.name as 'groupname',
		phone.phone as 'mobilephone',
		contacts.e_mail as 'email',
		groups.group_id as 'congroup',
		contacts.contact_id
	 FROM	contacts, 
		groups, 
		phone
	 WHERE	(phone.supplier_id = 5388 or phone.supplier_id = 8747)
		AND phone.location = 2
		AND phone.contact_id = contacts.contact_id
		AND contacts.group_id = groups.group_id) as ContactInfo
	ON	ContactInfo.contact_id = ContactPhone.contact_id
	LEFT OUTER JOIN
	(SELECT  DISTINCT 
		b.contact_id, 
		(b.first_name +' '+ b.last_name) as 'Name',
		b.e_mail as 'email',
		memberships.group_id as 'memgroup'
	 FROM	contacts a, 
		contacts b, 
		phone, 
		memberships
	 WHERE	phone.location = 2
		AND (phone.supplier_id = 5388 or phone.supplier_id = 8747)
		AND phone.contact_ID = a.contact_id
		AND a.group_id = memberships.group_id
		AND memberships.contact_id = b.contact_id
		AND memberships.admin_flag = 1) as ManagerInfo
	ON 	ManagerInfo.memgroup = ContactInfo.congroup
	LEFT OUTER JOIN
	(SELECT	DISTINCT
		phone.phone as 'office',
		phone.contact_id
	 FROM	phone
	 WHERE 	phone.location = 1
		AND phone.phone_use <> 'Email') as ManagerPhone
	ON 	ManagerPhone.contact_id = ManagerInfo.contact_id
	
	
ORDER 	BY ContactInfo.Name

Code (markup):