Table1: 'tbl_partnerhirerege'
columns: seskey(pk) compannyname shortname address country customer
1 IBM PVTLMTD IBM BANGALORE INDIA IBMINDIA
Table2: 'tbl_quotation'
columns: seskey(pk) cutomer_id(fk) quotation_id(fk) rate no_of_test amount
1 1 1 10 10 100
Table3: 'tbl_quotation_master'
columns: seskey(pk) cutomer_id(fk) quotation_date
1 1 IBM16042012
Above are my three tables. Now i should join above three tables based on quotaion_id. And from table1 shortname column and from table3 quotation_date column should be concatenate.
Anybody please tell me. Its very urgent. I am waitning for your reply.
Thank you
vsp.santu
Member
157 Points
425 Posts
join for three tables and concatenate two columns
Apr 16, 2012 04:58 PM|LINK
Table1: 'tbl_partnerhirerege'
columns: seskey(pk) compannyname shortname address country customer
1 IBM PVTLMTD IBM BANGALORE INDIA IBMINDIA
Table2: 'tbl_quotation'
columns: seskey(pk) cutomer_id(fk) quotation_id(fk) rate no_of_test amount
1 1 1 10 10 100
Table3: 'tbl_quotation_master'
columns: seskey(pk) cutomer_id(fk) quotation_date
1 1 IBM16042012
Above are my three tables. Now i should join above three tables based on quotaion_id. And from table1 shortname column and from table3 quotation_date column should be concatenate.
Anybody please tell me. Its very urgent. I am waitning for your reply.
Thank you
tdmca
Contributor
2396 Points
661 Posts
Re: join for three tables and concatenate two columns
Apr 16, 2012 05:05 PM|LINK
on the basis of which column you want to join tables
and you are having problem in joining or concatenation
vsp.santu
Member
157 Points
425 Posts
Re: join for three tables and concatenate two columns
Apr 16, 2012 05:06 PM|LINK
customer_id base
TabAlleman
All-Star
15575 Points
2702 Posts
Re: join for three tables and concatenate two columns
Apr 16, 2012 05:10 PM|LINK
SELECT *, shortname+' '+quotation_date FROM tbl_partnerhirerege t1
INNER JOIN tbl_quotation t2 ON t1.seskey=t2.cutomer_id
INNER JOIN tbl_quotation_master t3 ON t2.quotation_id=t3.seskey
tdmca
Contributor
2396 Points
661 Posts
Re: join for three tables and concatenate two columns
Apr 16, 2012 05:14 PM|LINK
i am considering first table is having customer_id
select t1.shortname+' '+t3.quotation_date from tbl_partnerhirerege t1 join tbl_quotation t2 on t1.customer_id=t2.customer_id
join tbl_quotation_master t3 on t2.customer_id=t3.customer_id
let me know the result
tusharrs
Contributor
3230 Points
668 Posts
Re: join for three tables and concatenate two columns
Apr 16, 2012 05:21 PM|LINK
Hi,
From the table structure it is clear that table2 and table3 are related with customer id
but what is the relation of table1 with table2 and table3 which is the column you can join
( Mark as Answer if it helps you out )
View my Blog
vsp.santu
Member
157 Points
425 Posts
Re: join for three tables and concatenate two columns
Apr 16, 2012 05:31 PM|LINK
HI
i am getting error when i execute @company=4
Msg 245, Level 16, State 1, Procedure SP_QUOTATION_JOIN, Line 14
Conversion failed when converting the varchar value 'IBMINDIA' to data type int.
vsp.santu
Member
157 Points
425 Posts
Re: join for three tables and concatenate two columns
Apr 16, 2012 05:34 PM|LINK
from table1 customer_id is 'BMINDIA' and from table2 customer_id 2 and from table3 customer_id 2
tusharrs
Contributor
3230 Points
668 Posts
Re: join for three tables and concatenate two columns
Apr 16, 2012 05:38 PM|LINK
Hi,
What is the datatype of table2 customerid and table3 customerid if it is numeric then
You cannot join varchar column to numeric column ,
so if it contains IBMINDIA and 1 it will give error for conversion
So, the solution is you have to make customerid of table3 and table3 to the same datatype as table1.customerid or varchar
( Mark as Answer if it helps you out )
View my Blog
tdmca
Contributor
2396 Points
661 Posts
Re: join for three tables and concatenate two columns
Apr 16, 2012 05:57 PM|LINK
select t1.shortname+' '+t3.quotation_date from tbl_partnerhirerege t1 join tbl_quotation t2 on t1.BMINDIA=t2.customer_id
join tbl_quotation_master t3 on t2.customer_id=t3.customer_id