•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 426,422 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,370 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 565 | Replies: 5
 |
•
•
Join Date: Sep 2007
Posts: 13
Reputation: 
Rep Power: 2
Solved Threads: 0
Newbie Poster
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
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
•
•
Join Date: Jul 2005
Location: Dallas, TX
Posts: 481
Reputation:
Rep Power: 4
Solved Threads: 19
Posting Pro in Training
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
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,165
Reputation:
Rep Power: 7
Solved Threads: 59
Or maybe:
select b.id, isnull(a.name,b.name) as [Name] from b left join a on b.[Name] = LEFT(a.[Name],1)
•
•
Join Date: Jul 2005
Location: Dallas, TX
Posts: 481
Reputation:
Rep Power: 4
Solved Threads: 19
Posting Pro in Training
that would add the assumption that the names in b are the first letter of the names in a
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,165
Reputation:
Rep Power: 7
Solved Threads: 59
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.
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.
•
•
Join Date: Sep 2007
Posts: 13
Reputation:
Rep Power: 2
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by campkev 
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.
Last edited by bhakti.thakkar : Jan 27th, 2008 at 11:36 pm.
|
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
•
•
•
•
ajax asp database decimal seperator thousand seperator when using sql server deleting records from ms sql table where columns have duplicate values developer development hacker management studio 2005 microsoft msdn news office security software sql sql cache dependency with polling-based invalidation survey vista
- Mysql Joins Assistance (MySQL)
- Wordpress, Joins and Indexes (MySQL)
- Sql Joins (MySQL)
- MS SQL Joins - newb assistance (MS SQL)
- Joins (MySQL)
- Problem with Rewriting Subqueries as Joins (Database Design)
- Problem with Rewriting Subqueries as Joins (MS SQL)
Other Threads in the MS SQL Forum
- Previous Thread: StoredProcedure @variable = table.field not working
- Next Thread: Wierd maintenance plan problem
