locked
How to retrieve values when child table has not entry in parentt able for specific value RRS feed

  • Question

  • Hi

    I have two tables Table 1 as parent table and Table2 as Child table. If my parent table has ID and Child table has entry corresponding to that ID, I will get result. If my parent table has ID and no entry in Child table for that entry I have to pick up values which are saved as parent id 0. That means Child becomes the parent in that case. I have written the query , just want to know whats the best way to write query.

    IF EXISTS (SELECT 1 FROM Table1 A
                        JOIN Table 2 B ON B.ParentID = A.ID AND A.ID = @ParentID)
            BEGIN
                SELECT
                        A.ID,
                        A.Name,
                        (CASE WHEN A.IsActive = 1 THEN 'Yes' ELSE 'No' END) AS IsActive,
                        B.ParentID,
                        B.Name
                FROM
                        Table1 A
                        JOIN Table2 B ON B.ParentID = A.ID
                        AND A.ID = @ParentID
            END
        ELSE
            BEGIN
                SELECT
                         A.ID,
                        A.Name,
                        (CASE WHEN A.IsActive = 1 THEN 'Yes' ELSE 'No' END) AS IsActive
                FROM
                         Table2 B
                WHERE    B.ParentID = 0 AND  @ParentID = (SELECT A.ID FROM  Table 2 D WHERE D.ID = 3)
            END

    Thanks!

    Monday, September 3, 2018 1:14 PM

All replies

  • Hi,

    You can utilize the capability of RIGHT OUTER JOIN(Refer Link#1).

    RIGHT OUTER JOIN Syntax,

    SELECT column_name(s)
    FROM table1
    RIGHT JOIN table2 ON table1.column_name = table2.column_name;


    Link#1 https://www.w3schools.com/sql/sql_join_right.asp

    HTH


    Hope this Helps!!!! Regards, Note: Please Mark As Answered if you satisfy with Reply.

    Monday, September 3, 2018 5:25 PM
  • Hi nikzk,

    Per your post, it seems that the following query statement is what you want.

    IF EXISTS (SELECT 1 FROM Table1 A
                         JOIN Table2 B ON B.ParentID = A.ID AND A.ID = @ParentID)
             BEGIN
                 SELECT
                         A.ID,
                         A.Name,
                         (CASE WHEN A.IsActive = 1 THEN 'Yes' ELSE 'No' END) AS IsActive,
                         B.ParentID,
                         B.Name
                 FROM
                         Table1 A
                         JOIN Table2 B ON B.ParentID = A.ID
                         AND A.ID = @ParentID
             END
         ELSE
             BEGIN
                 SELECT
                         A.ID,
                         A.Name,
                         (CASE WHEN A.IsActive = 1 THEN 'Yes' ELSE 'No' END) AS IsActive,
    					 ISNULL(B.ParentID,0) AS ParentID,
                         ISNULL(B.Name,'') AS [B_Name]
                 FROM 
    			          Table1 A
    					  LEFT JOIN Table2 B ON B.ParentID = A.ID
                          --Table2 B
                 --WHERE    B.ParentID = 0 AND  @ParentID = (SELECT A.ID FROM  Table 2 D WHERE D.ID = 3)
    			          WHERE A.ID = @ParentID
             END

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 4, 2018 8:08 AM
  • I think as per your explanation what you would need is something like this

    SELECT
                        A.ID,
                        A.Name,
                        (CASE WHEN A.IsActive = 1 THEN 'Yes' ELSE 'No' END) AS IsActive,
                        COALESCE(B.ParentID,0),
                        COALESCE(B.Name,B1.Name)
                FROM
                        Table1 A
                        LEFT JOIN Table2 B ON B.ParentID = A.ID
                        AND A.ID = @ParentID
                        CROSS JOIN Table2 B1 
                        WHERE B1.ParentID = 0
    I assume you would have only single entry with ParentID = 0 in Table 2. Otherwise you would need to have some other column also to be specified in the JOIN condition to avoid duplicates being returned i.e all rows with ParentID = 0 returned for every row in Table1 without a matching entry in Table2

    There's no need of any IF EXISTS check in this case


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, September 4, 2018 8:22 AM
  • >> I have two tables Table1 as Parent [sic] table and Table2 as Child [sic] table. <<

    NO! please read a book on RDBMS. The terms “Parent” and “Child [sic]” have nothing whatsoever to do with the relational model. They are taken from old pointer databases like IMS or IDMS. We do have “referenced” and “referencing” tables whose relationship is enforced in the DDL.

    But you fail to post DDL, in violation of basic netiquette!! and what you did post is some of the worst <g class="gr_ gr_69 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="69" id="69">modeling</g> that anyone could put in a system. There is no such thing as a generic “id” in RDBMS. Since the relational model is based on logic, it follows the Law of Identity. That means an identifier has to be the identifier. 

    Next, a data element should have one and only one name in a properly designed schema. That means it is never a “parent_id” in one place which magically mutates into a generic “nothing in particular identifier” somewhere else. Things like “_id” and “_name” are called attribute properties by ISO. They have to be attached to an attribute or column name. 

    Again, you didn’t post any DDL. 
    you got to your code, it’s procedural! But SQL is a declarative language. We hate loops, local variables and flow control statements like if-then-else. We also do not use assembly language flags like your non-relational “is_active” that you’re using to control your procedural code. If you get a book on basic software engineering, look up the term “flag coupling” as a bad programming method

    >> If my Parent [sic] table has ID [sic] and Child [sic] table has entry corresponding to that ID [sic], I will get result. If my Parent [sic] table has ID [sic] and no entry in Child [sic] table for that entry I have to pick up values which are saved as Parent [sic] id = 0 [sic]. <<

    why do you think <g class="gr_ gr_59 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling only-del replaceWithoutSep" data-gr-id="59" id="59">that that using</g> names like “table_1” and “table_2” are a good idea? They don’t tell us what these entities are. 

    When you read that book on data modeling, you’ll find out about different kinds of measurements. An identifier is measured with what is called a nominal scale. That means it can’t be numeric because you do no math on it.

    >> That means Child [sic] becomes the Parent [sic] in that case. <<

    No, a referenced able is a completely separate collection of entities from a referencing table. let’s go ahead and make this a little more realistic and use the textbook example of orders and order details.

    CREATE TABLE Orders
    (order_number CHAR(10) NOT NULL PRIMARY KEY,
     …);

    --- create a dummy referenced order
    INSERT INTO Orders VALUES (‘000000000’, ...)

    CREATE TABLE Order_Details
    (order_number CHAR(10) DEFAULT ‘000000000’ NOT NULL
     REFERENCES Orders (order_number)
     ON UPDATE CASCADE
     ON UPDATE DELETE,
     item_gtin CHAR(15) NOT NULL,
     PRIMARY KEY (order_number, item_gtin),
     …);

    See how the REFERENCES clause work? I put in a DEFAULT value for the order number, which you will have to add to the Orders table is a dummy value. Now all the data integrity is maintained by the SQL engine, declaratively without the use of <g class="gr_ gr_65 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="65" id="65">procedural</g> code.

    >> I have written the query, just want to know whats the best way to write <g class="gr_ gr_61 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="61" id="61">query</g>. <<

    Not to write it at all. Also, while it will still work, writing “EXISTS (SELECT 1 FROM ..” is considered bad programming. It’s a <g class="gr_ gr_58 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" data-gr-id="58" id="58">left over</g> from some stuff written in the 1980s in the first Oracle compilers. The preferred form is “EXISTS (SELECT * FROM ..<g class="gr_ gr_75 gr-alert gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep" data-gr-id="75" id="75">”instead</g>.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, September 4, 2018 8:24 PM
  • No, there will be multiple entries. It is like two tables category and sub category

    I there is no subcategory for a category, we have to pick all the values from subcategory table with CategoryID = 0

    Its like

    Category Table                                                                                 Subcategory Table

    ID          Name         IsActive                           ID         Name                IsActive         CategoryID

    1           cat1                1                                  1       Subcat1                     1                   1 

    2           cat2                1                                  2        Subcat2                    1                   2

    3           cat3                1                                  3         Subcat3                    1                  0

                                                                            4         Subcat4                   1                    0
    • Edited by nikzk Thursday, September 6, 2018 9:29 AM
    Thursday, September 6, 2018 9:26 AM
  • No, there will be multiple entries. It is like two tables category and sub category

    I there is no subcategory for a category, we have to pick all the values from subcategory table with CategoryID = 0

    Its like

    Category Table                                                                                 Subcategory Table

    ID          Name         IsActive                           ID         Name                IsActive         CategoryID

    1           cat1                1                                  1       Subcat1                     1                   1 

    2           cat2                1                                  2        Subcat2                    1                   2

    3           cat3                1                                  3         Subcat3                    1                  0

                                                                            4         Subcat4                   1                    0

    So the statement is like this.

    If exists (select 1 from Category T where not exists (select 1 from Subcategory where  CategoryID=T.ID))
    begin
    
    select ID,Name,IsActive,CategoryID from Subcategory where CategoryID=0
    end

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 6, 2018 9:47 AM
  • No, there will be multiple entries. It is like two tables category and sub category

    I there is no subcategory for a category, we have to pick all the values from subcategory table with CategoryID = 0

    Its like

    Category Table                                                                                 Subcategory Table

    ID          Name         IsActive                           ID         Name                IsActive         CategoryID

    1           cat1                1                                  1       Subcat1                     1                   1 

    2           cat2                1                                  2        Subcat2                    1                   2

    3           cat3                1                                  3         Subcat3                    1                  0

                                                                            4         Subcat4                   1                    0

    So there will be multiple entries with CategoryID = 0 in subcategory table?

    In that case for case of category without subcategory you need all those entries right?

    then this should work

    SELECT c.ID,
    c.Name,
    CASE WHEN c.IsActive = 1 THEN 'Yes' ELSE 'No' END AS IsActive,
    s.Name,
    s.CategoryID
    FROM Category c
    INNER JOIN Subcategory s
    ON s.CategoryID = c.ID
    
    UNION ALL
    
    SELECT c.ID,
    c.Name,
    CASE WHEN c.IsActive = 1 THEN 'Yes' ELSE 'No' END AS IsActive,
    s.Name,
    s.CategoryID
    FROM Category c
    CROSS JOIN Subcategory s
    WHERE s.CategoryID = 0
    AND NOT EXISTS
    (
    SELECT 1
    FROM Subcategory 
    WHERE CategoryID = c.ID
    )


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, September 6, 2018 9:51 AM