sqlsql-serversql-server-2008-r2

How to rewrite this as a nested query?


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.

enter image description here


Solution

  • 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