quote:

---

Originally posted by atulkamat2008

Hello all

I am running a query where the same table is join 4 times. Due to this number of records goes to millions. It take more than 5 min for the query to run. I am trying to optimize the query.
Anybody please help me in the regards

Thank You

---

...
LEFT JOIN	Telephone AS t1 ON t1.CustomerID = c.CustomerID
			AND t1.Type = 'Home'
LEFT JOIN	Telephone AS t2 ON t2.CustomerID = c.CustomerID
			AND t2.Type = 'Work'
LEFT JOIN	Telephone AS t3 ON t3.CustomerID = c.CustomerID
			AND t3.Type = 'Mobile'
LEFT JOIN	Telephone AS t4 ON t4.CustomerID = c.CustomerID
			AND t4.Type = 'Fax'

...
LEFT JOIN	(

			SELECT		CustomerID,
					MAX(CASE WHEN Type = 'Home' THEN Phone ELSE NULL END) AS Home,
					MAX(CASE WHEN Type = 'Work' THEN Phone ELSE NULL END) AS Work,
					MAX(CASE WHEN Type = 'Mobile' THEN Phone ELSE NULL END) AS Mobile,
					MAX(CASE WHEN Type = 'Fax' THEN Phone ELSE NULL END) AS Fax
			GROUP BY	CustomerID
		) AS t ON t.CustomerID = c.CustomerID