SQL Server Performance

Query to fetch missing siblings

Discussion in 'SQL Server 2005 General Developer Questions' started by SATISD9X, Dec 16, 2012.

  1. SATISD9X New Member

    Code:
    
                DECLARE @AName        VARCHAR(100)
                DECLARE @BName        VARCHAR(100)
                DECLARE @CName        VARCHAR(100)
    
                SET @AName            = ''
                SET @BName            = ''
                SET @CName            = ''
     
                SELECT
                    ChildsChildId,
                    ChildsChildName,
                    ParentsChildId,
                    ParentsChildName,
                    ParentId,
                    ParentName
                FROM
                    (
                        SELECT
                                ''                        AS 'ChildsChildId',
                                ''                        AS 'ChildsChildName',
                                b.Id                    AS 'ParentsChildId',
                                b.[Name]                AS 'ParentsChildName',
                                p.Id                    AS 'ParentId',
                                p.[Name]                AS 'ParentName'
                        FROM [sch].[ParentsChild] b
                            INNER JOIN [sch].[Parent] a
                                    ON a.Id = b.Id
                        WHERE NOT EXISTS
                        (
                            SELECT *
                                FROM [sch].[ChildsChild] c
                                WHERE b.Id = c.Id
                                  AND c.Id IS NOT NULL
                                  AND c.Active = 1
                        )
                        AND b.Active = 1
                        AND a.Active = 1
    
                    UNION
    
                        SELECT
                                c.Id        AS 'ChildsChildId',
                                c.[Name]    AS 'ChildsChildName',
                                b.Id        AS 'ParentsChildId',
                                b.[Name]    AS 'ParentsChildName',
                                a.Id        AS 'ParentId',
                                a.[Name]    AS 'ParentName'
                        FROM    [sch].[ChildsChild] c RIGHT JOIN [sch].[ParentsChild] b
                            ON b.Id = c.Id
                                INNER JOIN [sch].[Parent] a
                            ON a.Id = b.Id
                        WHERE    a.Active = 1
                                AND b.Active = 1
                                AND c.Active = 1
    
                ) a
                WHERE        (a.ParentName            = @AName    OR ISNULL(@AName, '') = '')
                        AND    (a.ParentsChildName        = @BName    OR ISNULL(@BName, '') = '')
                        AND (a.ChildsChildName        = @CName    OR ISNULL(@CName, '') = '')
                

    I've this above query where I need to fetch all the records from the three tables.
    First table [sch].[Parent] has a field referenced as Foreign Key in second table [sch].[ParentsChild]. There are records which are in both the tables with their FK.
    Now, there is a third table [sch].[ChildsChild] which is also referenced by FK of second table [sch].[ParentsChild] table, but there are records missing for the entries in second table.
    So now I need to fetch all the records from three tables including JOINS for all three tables and for those also whose records are missing from third table i.e. [sch].[ChildsChild] table.
    I've the query, but need to know if there is any other COST effective query than the below one.

Share This Page