Hi
can any one help me in following case?
Table A

ID | Name
1 , Apple
2 , Bat
3 , Cat
4 , Dog

Table B
ID | Name

1 , A
2 , B
3 , C
4 , D
5 , E
6 , F
7 , G

How will i get the below result??

Results

ID | Name

1 , Apple
2 , Bat
3 , Cat
4 , Dog
5 , E
6 , F
7 , G
How can I achieve this.
Thanks

3
Contributors
5
Replies
6
Views
10 Years
Discussion Span
Last Post by bhakti.thakkar

ok, i am assuming that you want a name for all id's. if a has an entry use that, otherwise use the name from b. also assuming that B has an entry for every id. if those assumptions are correct, then this should work

``````select b.id, isnull(a.name,b.name) as [Name] from b
left join a on b.id = a.id``````

Or maybe:

``````select b.id, isnull(a.name,b.name) as [Name] from b
left join a on b.[Name] = LEFT(a.[Name],1)``````

that would add the assumption that the names in b are the first letter of the names in a

Hmm not really, it replaces the assumption that a.id is a foreign key to b.id.

The OP does not specify. If there is a relation between the id's (which there should be in my opinion, and if the data provided represents all possibilities, then this is indeed the case) your solution is the more efficient.

However if the id's are not related then that only leaves the initial letters, in which case my alternate solution will do.

ok, i am assuming that you want a name for all id's. if a has an entry use that, otherwise use the name from b. also assuming that B has an entry for every id. if those assumptions are correct, then this should work

``````select b.id, isnull(a.name,b.name) as [Name] from b
left join a on b.id = a.id``````

Thanks to reply my query. It worked. :)

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.