October 12, 2015 at 4:50 am
I want to join 2 tables, table a and table b where b is a lookup table by left outer join. my question is how can i generate a flag that show whether match or not match the join condition ?
**The lookup table b for column id and country are always not null values, and both of them are the keys to join table a. This is because same id and country can have multiples rows in table a due to update date and posting date fields.
example table a
id country area
1 China Asia
2 Thailand Asia
3 Jamaica SouthAmerica
4 Japan Asia
example table b
id country area
1 China Asia
2 Thailand SouthEastAsia
3 Jamaica SouthAmerica
5 USA America
Expected output
id country area Match
1 China Asia Y
2 Thailand SouthEastAsia Y
3 Jamaica SouthAmerica Y
4 Japan Asia N
October 12, 2015 at 5:13 am
sqlbaby2 (10/12/2015)
I want to join 2 tables, table a and table b where b is a lookup table by left outer join. my question is how can i generate a flag that show whether match or not match the join condition ?example table a
id country area
1 China Asia
2 Thailand Asia
3 Jamaica SouthAmerica
4 Japan Asia
example table b
id country area
1 China Asia
2 Thailand SouthEastAsia
3 Jamaica SouthAmerica
Expected output
id country area Match
1 China Asia Y
2 Thailand SouthEastAsia Y
3 Jamaica SouthAmerica Y
4 Japan Asia N
If there has not been a match, the Id column from table b will be NULL. Use this fact to generate your Match column.
select ,,, Match = iif(b.Id is null,'N', 'Y')
from tableA a
left join tableB b on a.Id = b.Id
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 12, 2015 at 5:17 am
id is null is not applicable in this situations because id is always not null values
October 12, 2015 at 5:47 am
sqlbaby2 (10/12/2015)
id is null is not applicable in this situations because id is always not null values
Table A has a row where Id = 4.
Table B does not have a row where Id = 4.
Therefore, a left join from table A to table B will return a NULL b.Id, where A.Id = 4.
If you do not agree with this statement, please provide an example to back up your assertion.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply