Query to fetch missing siblings | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query to fetch missing siblings

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.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |