June 19, 2011 at 3:56 pm
Hello
I need to join Table A with
Table B or Table C based on condition
My Table A consists a column with ID's starting with either A12344566 or 1234566
if the id starts with A it should be joined with table B o
if the id starts with number it should be joined with table c
I am trying to do it in the below way
Select *
From TableAA A
Join TableB B On A.id = B.id and Substring(A.id,1,1)='A'
Join TableC C On A.id = C.id and Substring(A.id,1,1)!='A'
June 19, 2011 at 4:07 pm
The following URL includes an example of Conditional Joins:
http://stackoverflow.com/questions/4522101/sql-server-2005-2008-conditional-join
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 19, 2011 at 4:07 pm
Sachin4all (6/19/2011)
HelloI need to join Table A with
Table B or Table C based on condition
My Table A consists a column with ID's starting with either A12344566 or 1234566
if the id starts with A it should be joined with table B o
if the id starts with number it should be joined with table c
I am trying to do it in the below way
Select *
From TableAA A
Join TableB B On A.id = B.id and Substring(A.id,1,1)='A'
Join TableC C On A.id = C.id and Substring(A.id,1,1)!='A'
This line appears to be incorrect
Join TableC C On A.id = C.id and Substring(A.id,1,1)!='A'
So what is your question? Have you tested your T-SQL and having a problem?
If so please post the table definitions, some sample data and someone will then be able to give you a tested reply.
To rapidly and easily post the above click on the first link in my signature block to learn how to do so with the sample T-SQL statements in the article.
June 19, 2011 at 10:28 pm
I personally do not like to use a function for the predicate. My call would be, create two temp table and load the data start with A to one table and number with other two table.
Once we have the two temp tables loaded, then join with the tableAA and unon the result.
Please let us know perhaps you found trouble to implement...
June 20, 2011 at 2:17 am
declare @table table
(
name varchar(256)
)
declare @tableA table
(
name varchar(256)
)
declare @tableB table
(
name varchar(256)
)
insert into @table
select 'A123'
union
select '123'
union
select '1234'
insert into @tableA
select 'A123'
insert into @tableB
select '123'
union
select '1234'
select a.* , coalesce(b.name, c.name) from @table a
left join @tableA b
on a.name =b.name
and b.name is not null
left join @tableB c
on a.name = c.name
and c.name is not null
Would this work for you.
June 20, 2011 at 6:07 am
Thanks Jayanth
its working fine....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply