Table joins - problem

[Former Member]

Hi All,

My scenario is Database - XI - R/3. In sender side I am using a JDBC adapter. The data I need to pull is present in 2 tables.

Consider my 1st table has the fields empid,empname and my 2nd table contains empid & project.

The values for empid field in table1 are unique but have multiple entries in table2.

Now myrequirement is to fetch a row from table1 and based on the empid value I have to fetch a row from table2. If multiple rows are found for a single empid then I have to create that many rows as my target.

Is it possible? If so can you explain it?

Thanks & Regards,

Jai Shankar.

[Former Member · Accepted solution]

Hi All,

Thanks for your inputs. Let me explain my scenario eith an example.

In table1 I have an empid say 5601. For this value 5601 I have multiple records in table2.

Example

Table1.

5601 Jai Shankar

Table2.

5601 XI

5601 EP.

Now my target record should be in this format. It should contain 2 records

5601 Jai Shankar XI

5601 Jai Shankar EP

The number of entries in table2 is unknown. It may be from 1 to many records.

Thanks & Regards,

Jai Shankar.

[Former Member · Accepted solution]

Hey,

<b>select table1.empid,table1.empname,table2.project from table1,table2 where table1.empid=table2.empid.</b>

this will give the desired output with list of project with their id,name and list of projects

cheers

jitheh

[bhavesh_kantilal · Accepted solution]

Jai,

Like mentioned, using the query in Jithesh's post will be able to select all the rows and send it to the XI.

Just make sure that your <b><ROW> is 1..UB</b> in your Datatype defintion.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi All,

Thanks for all your help. My issue got resolved with your help.

Thanks & Regards,

Jai Shankar.

[Former Member · Accepted solution]

Hi Jai,

Please can you let us know your select query that worked??

Regards

Vijaya

[Former Member · Accepted solution]

Hi Vijaya,

I tried the query suggested by Jitesh.

Here is my actual querry.

select table1.Emp_Id,table1.Emp_Name,table2.Project from table1,table2 where table1.Emp_Id=table2.Emp_Id and table2.Status = '1'

and my update querry is

update table2 set Status = '0' where Status = '1'

Hope this helps you.

Thanks & Regards,

Jai Shankar.

[Former Member]

Hi Jai,

Try using Jithesh's example posted earlier with t2.* in the select query.

Hope this helps.

cheers,

Prashanth

P.S Please mark helpful answers

[Former Member]

Hi Jai,

As far as my understanding, u need to select data from two of ur tables based on emp id rite?Why dont u Use a select, from, where statement to do this?

cheers,

Prashanth

P.S Please mark helpful answers

[Former Member]

Hey,

y don u try this query in ur sender JDBC adapter

select t1.empid,t1.empname,t2.project from t1,t2 where t1.empid=t2.empid

cheers

jithesh

[Former Member]

Hi,

You can very well do this. In the datatypes you create your project details will be an unbounded one where as employee name will occur only once...i mean it will be some thing like this.

<empid>

<empname>

<project></project>

<project><project>

</empname>

</empid>

Am I Right?? The empid tag also can occur multiple times depending upon your requirement. write a query that will fetch the required values and map them to corresponding fields.

Regards,

Sudheer.

[Former Member]

Hi ,

use this statement in the sender JDBC adapter...then you can select the data from 2 tables...

SELECT table1.,table2.

FROM table1,table2

WHERE table1.empid=table2.empid

sekhar

[bhavesh_kantilal]

Jai,

Am not sure I understand the entire flow.

In your Sender JDBC adapter, you want to select data from multiple tables , or , you need to get a row from your table 1 and then for each row of table1 retrieve values from table2??

Can you give an example..

Regards,

Bhavesh