topic Re: Select statement with mulitple joins in Application Development and Automation Discussions

Select statement with mulitple joins

Former Member — Mon, 15 Mar 2010 13:17:07 GMT

Hi,

I not able to do a select statement invloving 3 tables and would much appreciate if you'll can help me get it working.

Here's the scenario:


select count(*) from table1 as a
   left join table2 as b on a~field1 = b~field1 
  join table3 on b~field2 = c~field2

All tables are custom tables. table1 is the item master data and field1 is its primary key.

table2 holds item status field1 and field2 combined form the unique key for this table. Not all items have status so I am doing a left join on table1 and table2.

table2 holds the text for different status, so a join between table2 and table3.

My ultimate aim is to bring all items from table1 for a particular criteria and status text (from table3) for items having status.

But the select statement above brings only items having records in table2 (that is records with valid status) i.e. it is treating the left join between table1 and table2 as an inner join.

Please suggest me the correct order of select statement.

Thank you,

Edited by: Rob Burbank on Mar 15, 2010 10:16 AM

Re: Select statement with mulitple joins

ThomasZloch — Mon, 15 Mar 2010 13:24:34 GMT

Please list your exact WHERE-conditions here. Since you are selecting by status text in table3, I think it makes sense that the result set contains only rows with a corresponding entry in table2, since table2 and table3 are connected via inner join, so you probably need two selects (or some OR condition) to obtain the final result.

Thomas

Re: Select statement with mulitple joins

Former Member — Mon, 15 Mar 2010 13:24:49 GMT

i would like to have separate internal tables first joining table 1 and table 2 , table 2 and table 3 and merge into final internal table . Use a describe statement to check the count.

//join table3 on b_{field2 = c}field2

there should be one link between table 1 and table 3 ?

-->check join table1 and table3 on what key field to remove the table field .

// select count(*) from table1 as a inner join table2 as b on a_{field1 = b}field1

join table3 on b_{field2 = c}field2 " <----link to table 1 in here

br,

vijay

Re: Select statement with mulitple joins

Former Member — Mon, 15 Mar 2010 13:27:46 GMT

Thanks for the reply, only where condition I have is on created date field in table1 and that does not seem to have an impact on the select behavior as tried removing it..

Re: Select statement with mulitple joins

Former Member — Mon, 15 Mar 2010 14:32:33 GMT

Hi,

As mentioned by you, I derived the structure of three tables as below:

ZTable1

Item

Date

ZTable12

Item

Status_ID

ZTable3

Status_ID

Status_Text

Based on this, your select statement should be

Select a_{Item c}Status_Text

into table itab

from ( ( ZTable1 AS a INNER JOIN ZTable2 AS b

on a_{Item = b}Item )

inner join ZTable3 as c on b_{Item_ID = c}Item_ID )

Where a~Date in/= .....

Regards,

Ni3

Re: Select statement with mulitple joins

Former Member — Mon, 15 Mar 2010 14:44:28 GMT

I dont want to do inner join between Table1 and Table2, reason being not all items contained in Table1 are contained in Table2

While the select should retrieve all items regardless they have status (i.e. they are contained in Table2 or not)

Re: Select statement with mulitple joins

former_member182670 — Mon, 15 Mar 2010 23:25:03 GMT

Hi,

You would need to create dictionary view to join table2 and table3.

The reason for that is you cannot have a join on the right side of other join - see documentation at [http://help.sap.com/saphelp_nw04/helpdata/EN/fc/eb39c4358411d1829f0000e829fbfe/frameset.htm|http://help.sap.com/saphelp_nw04/helpdata/EN/fc/eb39c4358411d1829f0000e829fbfe/frameset.htm]. Only db table or view is allowed on the right side.

so your scenario:

table1 left outer join (table2 join table3)

is not possible directly in Open SQL.

Assuming you create view which joins table2 and table3 and call it zview1 you can do:

table1 left outer join zview1

PS. I'm also assuming that you want the data not just count(*) as this would be equal to count of records in table1 fro this query

Re: Select statement with mulitple joins

Pranay_Panchbha — Tue, 16 Mar 2010 08:15:26 GMT

HIIIIIIIIIII CD CHECK THIS CODE AND MODIFY AS PE UR FIELDS OF SELECTION MAY THIS USEFUL FOR YOU

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE Text-001.

PARAMETERS P_BUKRS LIKE BSID-BUKRS DEFAULT '0010'.

SELECT-OPTIONS: S_KTOKD FOR KNA1-KTOKD MEMORY ID KGD,

S_KUNNR FOR KNA1-KUNNR,

S_BLDAT FOR BSID-BLDAT.

SELECTION-SCREEN END OF BLOCK B1.

START-OF-SELECTION.

SELECT A_{KUNNR A}NAME1 A_{KTOKD B}AUGBL B_{XBLNR B}ZUONR

B_{BLDAT B}BLART B_{BUDAT B}SHKZG B_{BELNR B}DMBTR B~SGTXT

INTO CORRESPONDING FIELDS OF TABLE IDATA

FROM KNA1 AS A INNER JOIN BSID AS B

ON B_{KUNNR EQ A}KUNNR

INNER JOIN BKPF AS C

ON C_{BUKRS EQ B}BUKRS AND

C_{BELNR EQ B}BELNR AND

C_{GJAHR EQ B}GJAHR

WHERE A~KUNNR IN S_KUNNR AND

A~KTOKD IN S_KTOKD AND

B~BUKRS EQ P_BUKRS AND

B~BLART NE 'DR' AND

B~BLDAT IN S_BLDAT .

Edited by: pranay panchbhai on Mar 16, 2010 1:45 PM

Re: Select statement with mulitple joins

Former Member — Tue, 16 Mar 2010 15:43:06 GMT

Thanks TMackowski for the reply.

Yes view is one option, I endend up using an internal table for table3 as it has very few records, and I in the program I am anyhow looping through the result set.