Skip to Content
author's profile photo Former Member
Former Member

Table joins - problem

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.

Add comment
10|10000 characters needed characters exceeded

7 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2006 at 06:08 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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.

  • Posted on Aug 02, 2006 at 05:34 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2006 at 05:38 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2006 at 05:40 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2006 at 06:12 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2006 at 05:41 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2006 at 05:43 AM

    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

    Add comment
    10|10000 characters needed characters exceeded