join for three tables and concatenate two columns

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

Re: join for three tables and concatenate two columns

on the basis of which column you want to join tables

and you are having problem in joining or concatenation

Re: join for three tables and concatenate two columns

customer_id base

Re: join for three tables and concatenate two columns

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

Re: join for three tables and concatenate two columns

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

Re: join for three tables and concatenate two columns

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

Re: join for three tables and concatenate two columns

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.

Re: join for three tables and concatenate two columns

from table1 customer_id is 'BMINDIA' and from table2 customer_id 2 and from table3 customer_id 2

Re: join for three tables and concatenate two columns

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

Re: join for three tables and concatenate two columns

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

Re: join for three tables and concatenate two columns

hi,

i changed into customerid as int datatype in table1. And my code is

ALTER procedure [dbo].[SP_QUOTATION_JOIN]
@company INT
as
--SELECT *, shortname+' '+quotation_date FROM tbl_partnerhirerege t1

--INNER JOIN tbl_quotation t2 ON t1.userdid=t2.userdid

--INNER JOIN tbl_quotation_master t3 ON t2.userdid=t3.userdid

but when i execute i am getting all userdid records

Actually if i will give @company=5

Then that particular record will be diplay based on that userdid. but iam getting all records. can you tell me what is the problem

Re: join for three tables and concatenate two columns

Is this your table structure?

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

From your table structure, table2 and table3 are related, both have customer_id but don't have a relation to table1. In you stored procedure you are using user_id. From where did it comes from? In your tables, there is no user_id column.

Re: join for three tables and concatenate two columns

Hi,

As per your first post you have following table structure

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

As per your last post you have specified a userdid field in the query, so i am confused with this two post

Ok let it be

If as per your first post if customerid is having datatype int in all the three tables,

and now the data will be numeric in all the customerid fields so you cannot store IBMINDIA in customerid .

Now your query will be,

SELECT t1.shortname+' '+convert(varchar(50),t3.quotation_date)

FROM tbl_partnerhirerege t1

INNER JOIN tbl_quotation t2 ON t1.customeridd=t2.customerid

INNER JOIN tbl_quotation_master t3 ON t2.customerid=t3.customerid

Re: join for three tables and concatenate two columns

sorry i changed userdid field in three tables, my tables strucutre is

Table1: 'tbl_partnerhirerege'
columns: seskey(pk) compannyname shortname address country userdid
1 IBM PVTLMTD IBM BANGALORE INDIA 1
Table2: 'tbl_quotation'
columns: seskey(pk) userdid(fk) quotation_id(fk) rate no_of_test amount
1 1 1 10 10 100
Table3: 'tbl_quotation_master'
columns: seskey(pk) userdid(fk) quotation_date
1 1 IBM16042012

Re: join for three tables and concatenate two columns

Hi,

Ok no problem simply use userdid instead of customerid

SELECT t1.shortname+' '+convert(varchar(50),t3.quotation_date)

FROM tbl_partnerhirerege t1

INNER JOIN tbl_quotation t2 ON t1.userdid=t2.userdid

INNER JOIN tbl_quotation_master t3 ON t2.userdid=t3.userdid

Re: join for three tables and concatenate two columns

hi, i used your code. its working fine. But

ALTER procedure [dbo].[SP_QUOTATION_JOIN]
@userid INT
as
SELECT t1.shortname+' '+convert(varchar(50),t3.quotation_date)

FROM tbl_partnerhirerege t1

INNER JOIN tbl_quotation t2 ON t1.userdid=t2.userdid

INNER JOIN tbl_quotation_master t3 ON t2.userdid=t3.userdid

when i execute @userid=8 then output is coming below

IBM Apr 4 2012 12:00AM
IBM Apr 4 2012 12:00AM
IBM Apr 4 2012 12:00AM
IBM Apr 4 2012 12:00AM
IBM Apr 4 2012 12:00AM
IBM Apr 4 2012 12:00AM
HP Apr 6 2012 12.00AM

But actually here HP Apr 6 2012 12.00AM in this row hp and date userdid is 9 in three tabels. But this record also coming when i execute @userdid=8. Can you tell me the problem

Re: join for three tables and concatenate two columns

hi, thank you so much all.

its working.

Re: join for three tables and concatenate two columns

Hi,

Change your stored procedure like below

ALTER procedure [dbo].[SP_QUOTATION_JOIN] @userid INT

SELECT t1.shortname+' '+convert(varchar(50),t3.quotation_date)

FROM tbl_partnerhirerege t1

INNER JOIN tbl_quotation t2 ON t1.userdid=t2.userdid

INNER JOIN tbl_quotation_master t3 ON t2.userdid=t3.userdid

where t1.userdid = @userid

Please mark it as answer if it helped you out