How to write a query by joining two tables?

Question

I have two tables:

Table 1:

 ID NAME
 1      ID1
 2      ID2
 3      ID3
 4      ID4
 5      ID5
 6      ID6
 7      ID7

Table 2:

Parent_ID   Child_ID
    1         2
    2         5
    2         3
    3         6

How do I write a query to get below output if I assign Parent_Id = 1 in where condition?

P_ID    NAME    Is_Group    Selected
 1      ID1       Yes         No
 2      ID2       Yes         Yes
 3      ID3       Yes         Yes 
 4      ID4       No          No
 5      ID5       No          Yes 
 6      ID6       No          Yes
 7      ID7       No          No

So, output mainly contains records from table one but also it need to have two additional columns.

Value in Is_Group column should be "Yes" if ID from Table 1 exists in Parent_ID column in Table 2. Value in Selected column should be "yes" if ID from Table 1 exists in Child_ID column in Table 2 and Parent_ID = 1 (like a cross reference).

In additional, I need to check if a Child_ID has any cross reference. For example In Table 2 Child_ID is 2 for Parent_Id 1, 2 also has 5 and 3 as child_Id so I need to have Selected column values as "Yes" for Id's 3 and 5 and so on.

Thanks in advance for your reply. Sorry for my English.

it's important to know which RDBMS you're actually using since you have both `mysql` and `sql-server` tags — JamieD77, Jan 08 '16 at 16:25
The usual method for doing this is to use a recursive hierarchical query, but the method for doing that varies wildly between databases. For SQL Server, you would use [a recursive CTE](https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx) to do that. For MySQL, you generally have to [write a stored procedure](http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql). — Bacon Bits, Jan 08 '16 at 16:31
Thanks for your reply. I need this in sql-server. I will try using recursive CTE to get desired output. — user2502961, Jan 08 '16 at 16:35

score 0 · Answer 1 · answered Jan 08 '16 at 16:45

This should give you the output you need.

It uses a recursive cte to get the hierarchy.

Then outer joins to the cte twice to determine if the the ID is a Group, or Selected by checking for null values

WITH    cte AS
(   
    SELECT  Parent_ID,
            Child_ID
    FROM    Table2
    WHERE   Parent_ID = 1
    UNION ALL
    SELECT  t2.Parent_ID,
            t2.Child_ID
    FROM    Table2 t2
            INNER JOIN cte ON t2.Parent_ID = cte.Child_ID
)
SELECT DISTINCT
        t1.*,
        (CASE WHEN grp.Parent_ID IS NULL THEN 'No'
                ELSE 'Yes'
            END) AS Is_Group,
        (CASE WHEN sel.Parent_ID IS NULL THEN 'No'
                ELSE 'Yes'
            END) AS Selected
FROM    Table1 t1
        LEFT JOIN cte grp ON t1.ID = grp.Parent_ID
        LEFT JOIN cte sel ON t1.ID = sel.Child_ID

The fact that you're selecting everything from Table1 regardless of the whether it's in the selected hierarchy or not would give you No for Is_Group for any ID's that are Parent_IDs, but not actually in the hierachy cte. To always determine if an ID is a Group or not, just left join to Table2 as grp instead of the cte.. like.

;WITH    cte AS
(   
    SELECT  Parent_ID,
            Child_ID
    FROM    Table2
    WHERE   Parent_ID = 1
    UNION ALL
    SELECT  t2.Parent_ID,
            t2.Child_ID
    FROM    Table2 t2
            INNER JOIN cte ON t2.Parent_ID = cte.Child_ID
)
SELECT DISTINCT
        t1.*,
        (CASE WHEN grp.Parent_ID IS NULL THEN 'No'
                ELSE 'Yes'
            END) AS Is_Group,
        (CASE WHEN sel.Parent_ID IS NULL THEN 'No'
                ELSE 'Yes'
            END) AS Selected
FROM    Table1 t1
        LEFT JOIN Table2 grp ON t1.ID = grp.Parent_ID
        LEFT JOIN cte sel ON t1.ID = sel.Child_ID

score 0 · Answer 2 · answered Jan 08 '16 at 17:42

try this,

select distinct id, t.NAME, 
    case when t1.Parent_ID is not null then 'Yes' else 'No' end Is_Group
     ,case when b.Child_ID is null then 'No' else 'Yes' end Selected 
     from Table1 t   left join    Table2 t1 on t.ID =t1.Parent_ID
      outer apply (select Child_ID from Table2 a where a.Child_ID=t.ID ) b

How to write a query by joining two tables?

2 Answers2