Getting same data from two diffrent tables

Select * from events where  job_dtls_id IN (select job_dtls_id from job_details where job_no='' and consignment_id='' and carrier_reference='');

Hello All,

I have to retrieve job_dtls_id (query in brckets ) in which it will match the above shown criteria (job_no,consignment_id and carrier_reference).

but the problem is job_no And consignment_id are in one table and carrier_reference is in another.

the two table have relation on job_dtls_id.

Do anybody have an Idea please Help.

Thanks.

March 29th, 2015 6:47am

Are you asking for help with the SQL or configuring BizTalk?

1. You need to JOIN the other table:

JOIN tableWithCarrierReference ON tableWithJobNo.job_dtls_id = tableWithCarrierReference.job_dtls_id

2. To use with BizTalk, it is always better to put all the SQL in a Stored Procedure as opposed to the Polling Statement in the Receive Location.

Free Windows Admin Tool Kit Click here and download it now
March 29th, 2015 10:33am

Are you asking for help with the SQL or configuring BizTalk?

1. You need to JOIN the other table:

JOIN tableWithCarrierReference ON tableWithJobNo.job_dtls_id = tableWithCarrierReference.job_dtls_id

2. To use with BizTalk, it is always better to put all the SQL in a Stored Procedure as opposed to the Polling Statement in the Receive Location.

  • Marked as answer by NILESH_AES 18 hours 17 minutes ago
March 29th, 2015 2:32pm

Are you asking for help with the SQL or configuring BizTalk?

1. You need to JOIN the other table:

JOIN tableWithCarrierReference ON tableWithJobNo.job_dtls_id = tableWithCarrierReference.job_dtls_id

2. To use with BizTalk, it is always better to put all the SQL in a Stored Procedure as opposed to the Polling Statement in the Receive Location.

  • Marked as answer by NILESH_AES Monday, March 30, 2015 12:45 PM
Free Windows Admin Tool Kit Click here and download it now
March 29th, 2015 2:32pm

Are you asking for help with the SQL or configuring BizTalk?

1. You need to JOIN the other table:

JOIN tableWithCarrierReference ON tableWithJobNo.job_dtls_id = tableWithCarrierReference.job_dtls_id

2. To use with BizTalk, it is always better to put all the SQL in a Stored Procedure as opposed to the Polling Statement in the Receive Location.

  • Marked as answer by NILESH_AES Monday, March 30, 2015 12:45 PM
March 29th, 2015 2:32pm

Hi Nilesh,

As suggested by John, having JOIN on the tables should give you the desired output and have it saved as stored procedure and invoke it from BizTalk, have a look at following post for reference: http://tech-findings.blogspot.com/2013/08/polling-data-from-sql-using-wcf-sql.html

___

Maheshkumar S Tiwari|User Page|Blog|How to Access an Orchestration variable in Map

Free Windows Admin Tool Kit Click here and download it now
March 30th, 2015 1:44am

Where exactly do you need to execute this query?

If you want to execute this query for polling data from a receive location- It should not be an issue, you will have to construct a query using Joins

Read the basics of Join here and here

By applying SQL Joins on releated fields(job_dtls_id in your case), you will be able to get required data.

If you want to execute this query from within the Orchestration you can refer the below article which gives step by step description of the same.

Performing ExecuteReader, ExecuteScalar, or ExecuteNonQuery Operations by Using BizTalk Server

March 30th, 2015 6:22am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics