I was told to rewrite thie following query with a nested query without joins for better performance. Is it true? If so, how to rewrite it?
SELECT distinct A.Company_Name,C.Outlet_Name,
C.Outlet_FCE_ID,D.Usergroup_Name
FROM company A, PURCHASE_INVOICE B,
Outlet C, User_Group D,CT_USER E
WHERE A.Company_ID = B.Company_ID AND B.Outlet_ID = C.Outlet_ID
AND B.Company_ID = C.Company_ID AND B.Username = E.Username
AND E.Usergroup_ID=D.Usergroup_ID
Here is the table structure.
Let me answer my own question. I don't like it this way, but here we go....
SELECT distinct
( SELECT Company_Name
FROM dbo.COMPANY
WHERE Company_ID = p.Company_ID
) AS 'CompanyName' ,
( SELECT Outlet_Name
FROM dbo.OUTLET
WHERE Company_ID = p.Company_ID
AND Outlet_ID = p.Outlet_ID
) AS 'OutletName' ,
( SELECT Outlet_FCE_ID
FROM dbo.OUTLET
WHERE Company_ID = p.Company_ID
AND Outlet_ID = p.Outlet_ID
) 'OutletFCEID' ,
( SELECT Usergroup_Name
FROM dbo.USER_GROUP
WHERE Usergroup_ID IN ( SELECT Usergroup_ID
FROM CT_USER
WHERE Username = p.UserName )
) 'UsergroupName'
FROM dbo.PURCHASE_INVOICE p