I have some record as under for tblFiltered
And the table tblComplete has the below data
The desired output is
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
whose output is
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
asked Jun 21 '11 at 04:30 AM in Default
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.
answered Jun 21 '11 at 04:53 AM