CODEWITHSUNDEEP
sqlsql-serversql-server-2008joinsql-server-2012
codeforce1
codeforce1

Reputation: 21

Left Outer join not returning expected resultSet

I have these two tables

              #TABLE1#
##CompanyName##    ##PrimaryKey##
DELL                     1
DELL CALIFORNIA          2
DELL SAN FRANCISCO       3
DELL LOS ANGELES         4
IBM                      5
GOOGLE                   6

                                     #TABLE2#
##ParentComp##   ##ParentPrimaryKey##       ##ChildComp##    ##ChildPrimaryKey##
    DELL                     1             DELL CALIFORNIA            2
DELL CALIFORNIA              2             DELL SAN FRANCISCO         3
DELL CALIFORNIA              2             DELL LOS ANGELES           4

Now the expected table is shown as below; the relationship(hierarchy) column has only three possible values(PARENT/CHILD/INDEPENDENT) as shown:

                               #TABLE3#
##CompanyName##             ##Relationship##                     ##ParentCompany##
DELL                             PARENT                                ---
DELL CALIFORNIA                  CHILD                                 DELL
DELL SAN FRANCISCO               CHILD                             DELL CALIFORNIA
DELL LOS ANGELES                 CHILD                             DELL CALIFORNIA
IBM                           INDEPENDENT                              ---
GOOGLE                        INDEPENDENT                              ---

I have already tried the following two join options

Select
...
Table1
Left Outer Join
Table2 ON Table1.PrimaryKey = Table2.ParentPrimaryKey
Left Outer Join
Table2 tAlias2
Table2.ParentPrimaryKey = tAlias2.ChildPrimaryKey

returns

 ##CompanyName##               ##Relationship##                     ##ParentCompany##
    DELL                             PARENT                                ---
    DELL CALIFORNIA                  CHILD                                 DELL
    DELL SAN FRANCISCO            INDEPENDENT                              ---
    DELL LOS ANGELES              INDEPENDENT                              ---
    IBM                           INDEPENDENT                              ---
    GOOGLE                        INDEPENDENT                              ---

or

Select
...
Table1
Left Outer Join
Table2 ON Table1.PrimaryKey = Table2.ChildPrimaryKey
Left Outer Join
Table1 tAlias1
Table2.ParentPrimaryKey = tAlias1.PrimaryKey

will return something like this:

 ##CompanyName##               ##Relationship##                     ##ParentCompany##
    DELL                           INDEPENDENT                             ---
    DELL CALIFORNIA                  CHILD                                 DELL
    DELL SAN FRANCISCO               CHILD                              DELL CALIFORNIA
    DELL LOS ANGELES                 CHILD                              DELL CALIFORNIA
    IBM                            INDEPENDENT                             ---
    GOOGLE                         INDEPENDENT                             ---

Upvotes: 1

Views: 106

Answers (2)

Alex Kudryashev
Alex Kudryashev

Reputation: 9470

Your schema is obviously redundant. You can drop table2 and receive all you want from following.

declare @tbl table (CompanyName varchar(100),PrimaryKey int primary key, parentId int)

insert @tbl values
('DELL',                     1,null),
('DELL CALIFORNIA',          2,1),
('DELL SAN FRANCISCO',       3,2),
('DELL LOS ANGELES',         4,2),
('IBM',                      5,null),
('google',                   6,null)


;with tbl as (
select PrimaryKey, CompanyName, parentId,
rel = cast(case when exists(select 1 from @tbl t1 where t1.parentId=t.PrimaryKey) then 'parent'
      else 'independent' end as varchar(100)),
cast(null as varchar(100)) parentName
from @tbl t
where t.parentId is null
union all
select t.PrimaryKey, t.CompanyName, t.parentId,cast('child' as varchar(100)),
tbl.CompanyName
from @tbl t inner join tbl on t.parentId=tbl.PrimaryKey
)
select * from tbl

order by PrimaryKey

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93764

When you join talias2.ChildPrimaryKey you need to join with Table1.PrimaryKey column. 

SELECT ..
FROM   table1 
       LEFT OUTER JOIN table2 
                    ON table1.primarykey = table2.parentprimarykey 
       LEFT OUTER JOIN table2 talias2 
                    ON table1.primarykey = talias2.childprimarykey --here

When you join tAlias2.ChildPrimaryKey with table2.parentprimarykey then only the matched records from table2 and table1(table1.primarykey = table2.parentprimarykey) will be joined with tAlias2.ChildPrimaryKey

Upvotes: 0

Related Questions