improve performance/enhance this query

hello, i'm attempting to see if i can enhance this query at all.. as you can see it's a little out of control.. i will give anyone who can help me with this an hi-five through the computer screen..

SELECT	--Phone Fields
	dbo.Telco.Group_ID			AS Phone_Supplier_ID,
	dbo.Telco.Name				AS Phone_Supplier,
	query_phone.Phone_ID			AS Phone_Phone_ID, 
	query_phone.phone			AS Phone_Phone,
	query_phone.Phone_Ext			AS Phone_Phone_Ext,
	query_phone.Location			AS Phone_Location, 
	query_phone.Phone_Use			AS Phone_Phone_Use,
	query_phone.LLC				AS Phone_PAD,
	query_phone.WPS				AS Phone_WPS, 
	query_phone.wps_denied			AS Phone_WPS_Requested,
	query_phone.PBX				AS Phone_Pbx,
	query_phone.Last_Update			AS Phone_Last_Update,
	--Client Fields
	query_phone.Contact_ID			AS Client_Contact_ID,
	client.Title				AS Client_Title,
	client.First_Name			AS Client_First_Name, 
	client.Last_Name			AS Client_Last_Name,
	client_group.Name			AS Client_Group,
	client.Jurisdiction			AS Client_Jurisdiction, 
	client.PSEP_Critical_Responsibility	AS Client_PSEP_Critical_Responsibility,
	client.Address				AS Client_Address,
	client.City				AS Client_City, 
	client.Postal_Code			AS Client_Postal_Code,
	client.Province				AS Client_Province,
	client.e_mail				AS Client_E_mail, 
	client.blackberry_pin			AS Client_Blackberry_PIN,
	client_phone.phone			AS Client_Office_Phone,
	client_phone_ext.phone_ext		AS Client_Office_Phone_Ext,
	client_mobile_phone.phone		AS Client_Mobile_Phone, 
	--Manager Fields
	manager.Contact_ID			AS Manager_Contact_ID,
	manager.Title				AS Manager_Title,
	manager.First_Name			AS Manager_First_Name, 
	manager.Last_Name			AS Manager_Last_Name,
	Manager_Group.Name			AS Manager_Group,
	manager.Jurisdiction			AS Manager_Jurisdiction, 
	manager.PSEP_Critical_Responsibility	AS Manager_PSEP_Critical_Responsibility,
	manager.Address				AS Manager_Address, 
	manager.City				AS Manager_City,
	manager.Postal_Code			AS Manager_Postal_Code,
	manager.Province			AS Manager_Province, 
	manager.e_mail				AS Manager_E_mail,
	manager.blackberry_pin			AS Manager_Blackberry_Pin,
	manager_phone.phone			AS Manager_Office_Phone,
	manager_phone_ext.phone_ext		AS Manager_Office_Phone_Ext,
	manager_mobile_phone.phone		AS Manager_Mobile_Phone

FROM	dbo.Phone query_phone LEFT OUTER JOIN dbo.Contacts client
		ON query_phone.Contact_ID = client.Contact_ID

	LEFT OUTER JOIN dbo.Groups client_group
		ON client.Group_ID = client_group.Group_ID

	LEFT OUTER JOIN
	(SELECT	MIN(phone.phone_id) AS 'phone_id',
		phone.contact_id
	 FROM	phone
	 WHERE	phone.location = 1
		AND phone.phone_use IN ('alt. ph.', 'trunk', 'Ph./Fax', 'Ph.')
	 GROUP BY phone.contact_id
	) client_phone_id
		ON query_phone.Contact_ID = client_phone_id.contact_id

	LEFT OUTER JOIN
	(SELECT	phone.phone AS 'phone',
		phone.phone_id
	 FROM	phone
	) client_phone
		ON client_phone_id.phone_id = client_phone.phone_id

	LEFT OUTER JOIN
	(SELECT	phone.phone_ext AS 'phone_ext',
		phone.phone_id
	 FROM	phone
	) client_phone_ext
		ON client_phone.phone_id = client_phone_ext.phone_id

	LEFT OUTER JOIN
	(SELECT	MIN(phone.phone) AS 'phone',
		phone.contact_id
	 FROM	phone
	 WHERE	phone.location = 2
		AND phone.phone_use IN ('alt. ph.', 'trunk', 'Ph./Fax', 'Ph.')
	 GROUP BY phone.contact_id
	) client_mobile_phone
		ON query_phone.Contact_ID = client_mobile_phone.contact_id

	LEFT OUTER JOIN
	(SELECT	MIN(contact_id) AS 'contact_id',
		memberships.group_id
	 FROM	memberships
	 WHERE	admin_flag = 1
	 GROUP BY memberships.group_id
	) memberships2
		ON client.Group_ID = memberships2.group_id

	LEFT OUTER JOIN dbo.Contacts manager
		ON memberships2.contact_id = manager.Contact_ID

	LEFT OUTER JOIN dbo.Groups Manager_Group
		ON manager.Group_ID = Manager_Group.Group_ID

	LEFT OUTER JOIN
	(SELECT	MIN(phone.phone_id) AS 'phone_id',
		phone.contact_id
	 FROM	phone
	 WHERE	phone.location = 1
		AND phone.phone_use IN ('alt. ph.', 'trunk', 'Ph./Fax', 'Ph.')
	 GROUP BY phone.contact_id
	) manager_phone_id
		ON manager.Contact_ID = manager_phone_id.contact_id

	LEFT OUTER JOIN
	(SELECT	phone.phone,
		phone.phone_id
	 FROM	phone
	) manager_phone
		ON manager_phone_id.phone_id = manager_phone.phone_id

	LEFT OUTER JOIN
	(SELECT	phone.phone_ext,
		phone.phone_id
	 FROM	phone
	) manager_phone_ext
		ON manager_phone.phone_id = manager_phone_ext.phone_id

	LEFT OUTER JOIN
	(SELECT	MIN(phone.phone) AS 'phone',
		phone.contact_id
	 FROM	phone
	 WHERE	phone.location = 2
		AND phone.phone_use IN ('alt. ph.', 'trunk', 'Ph./Fax', 'Ph.')
	 GROUP BY phone.contact_id
	) manager_mobile_phone
		ON manager.Contact_ID = manager_mobile_phone.contact_id

	LEFT OUTER JOIN dbo.Telco
		ON dbo.Telco.Group_ID = query_phone.Supplier_ID

Code (markup):

soooo this is a view that often comes in handy throughout my site.. it's not such a big deal that it takes a while to run cause i can just cache the results..

my big problem is having to use the MIN aggregate... it doesn't make sense that i would want the smallest phone id.. it was just the only thing i could think of.. for instance.. if we take this chunk..

LEFT OUTER JOIN
	(SELECT	MIN(phone.phone_id) AS 'phone_id',
		phone.contact_id
	 FROM	phone
	 WHERE	phone.location = 1
		AND phone.phone_use IN ('alt. ph.', 'trunk', 'Ph./Fax', 'Ph.')
	 GROUP BY phone.contact_id
	) client_phone_id
		ON query_phone.Contact_ID = client_phone_id.contact_id

	LEFT OUTER JOIN
	(SELECT	phone.phone AS 'phone',
		phone.phone_id
	 FROM	phone
	) client_phone
		ON client_phone_id.phone_id = client_phone.phone_id

	LEFT OUTER JOIN
	(SELECT	phone.phone_ext AS 'phone_ext',
		phone.phone_id
	 FROM	phone
	) client_phone_ext
		ON client_phone.phone_id = client_phone_ext.phone_id

Code (markup):

what i'm trying to do is return a single phone (office phone indicated by phone.location = 1) i would like to have the results ordered by the phone_use field and take the top 1 row.. however if i change it to

(select top 1 phone.phone_id
 from phone
 where phone.location = 1
	AND phone.phone_use IN ('alt. ph.', 'trunk', 'Ph./Fax', 'Ph.')
 order by phone.phone_use) as client_phone_id

Code (markup):

i'm only going to get one phone_id for all the clients not one each..

putting the select statements into the select list instead
i.e.

select queryphone.contact_id,
    (select top 1 phone.phone_id
 from phone
 where phone.location = 1
	AND phone.phone_use IN ('alt. ph.', 'trunk', 'Ph./Fax', 'Ph.')
        AND phone.contact_id = queryphone.contact_id
 order by phone.phone_use) as client_phone_id 

Code (markup):

this gave me the desired results but slowed down the query significantly

just wondering if anyone had any quick ideas

 

If you just need one row, adding "LIMIT 1" to the end of your query could help?