Left outer join produces null for ID column

Question

I have some record as under for tblFiltered

ID  RowID    Position   Data
1   1 a1  R
1   1 b1  N
1   1 e1  K
1   1 g1  N
1   1 h1  R
1   2 b2  T
1   2 c2  B
1   2 d2  Y
1   2 f2  F

And the table tblComplete has the below data

RowID   Position  Data
1   a1    NULL
1   b1    NULL
1   c1    NULL
1   d1    NULL
1   e1    NULL
1   f1    NULL
1   g1    NULL
1   h1    NULL
2   a2    NULL
2   b2    NULL
2   c2    NULL
2   d2    NULL
2   e2    NULL
2   f2    NULL
2   g2    NULL
2   h2    NULL

The desired output is

ID  RowID    Position  CompleteData
1   1 a1  R
1   1 b1  N
1   1 c1  NULL
1   1 d1  NULL
1   1 e1  K
1   1 f1  NULL
1   1 g1  N
1   1 h1  R
1   2 a2  NULL
1   2 b2  T
1   2 c2  B
1   2 d2  Y
1   2 e2  NULL
1   2 f2  F
1   2 g2  NULL
1   2 h2  NULL

That means the record that are not in tblFiltered will be taken from tblCompelete and it will be filled. e.g. c1,d1,f1 are not there in tblFiltered and henceforth are taken from tblCompelte and being added to the reaultant output.

I have written the query

select t.ID,c.RowID,c.Position,
 CompleteData = case when t.Data IS null then null else t.data end
from @CompleteData c
left join @tblFiltered t 
on c.Position = t.Position

whose output is

ID  RowID    Position   CompleteData
1   1 a1  R
1   1 b1  N
NULL    1  c1   NULL
NULL    1  d1   NULL
1   1 e1  K
NULL    1  f1   NULL
1   1 g1  N
1   1 h1  R
NULL    2  a2   NULL
1   2 b2  T
1   2 c2  B
1   2 d2  Y
NULL    2  e2   NULL
1   2 f2  F
NULL    2  g2   NULL
NULL    2  h2   NULL

As can be make out that, I am unable to set the ID.... Also if the ID's becomes > 1 in tblFiltered, then how will I keep track of that.

Thanks in advance

Answer 1

By using t.ID, you only get an ID if it shows up in tblFiltered. It looks like you may want to use an ISNULL or COALESCE statement if you are supposed to fill in a default value for ID. Otherwise, there is no way of getting a "correct" ID. So if ID on tblFiltered changes and is not deterministic (e.g., three-character position values have an ID of 2, two-character position values have an ID of 1), you cannot get a "correct" value because you will not know what it is.

The general idea behind a join is that, for each row of the main table, you are looking for any "matching" rows on the other table. For a left outer join, you return the main table rows regardless of whether there are any matches. In the event that there is not a match, a fake row (of sorts) on the other table is created, and that fake row is filled with all NULL values.

Left outer join produces null for ID column

Follow this question

Related Questions