topic Re: Inner Join Question in Application Development and Automation Discussions

Inner Join Question

Former Member — Wed, 17 May 2006 12:32:12 GMT

I am looking at a sample question for the ABAP certification exam. Either I am reading the question incorrectly, or I missed an important concept. I'd appreciate someone to check me here:

What happens when a 1 to many relationship is encountered with an inner join?

A.  Redundant data from the right-hand table is included.
B.  Redundant data from either table is not returned to the result set.
C.  Redundant data from the left-hand table is included.
D.  Runtime error.

I chose C, but the answer key says B. Did I miss an important concept, or is the answer key wrong?

Re: Inner Join Question

suresh_datti — Wed, 17 May 2006 12:36:41 GMT

Hi Jerry,

With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view. So the answer is B.

Regards,

Suresh Datti

Re: Inner Join Question

Former Member — Wed, 17 May 2006 12:37:23 GMT

go thru this

<b>select * FROM tabref1 [INNER] JOIN tabref2 ON cond</b>

effect

The data is to be selected from transparent database tables and/or views determined by tabref1 and tabref2. tabref1 and tabref2 each have the same form as in variant 1 or are themselves Join expressions. The keyword INNER does not have to be specified. The database tables or views determined by tabref1 and tabref2 must be recognized by the ABAP Dictionary.

In a relational data structure, it is quite normal for data that belongs together to be split up across several tables to help the process of standardization (see relational databases). To regroup this information into a database query, you can link tables using the join command. This formulates conditions for the columns in the tables involved. The inner join contains all combinations of lines from the database table determined by tabref1 with lines from the table determined by tabref2, whose values together meet the logical condition (join condition) specified using ON>cond.

Inner join between table 1 and table 2, where column D in both tables in the join condition is set the same:

Table 1 Table 2

|--|||--|

\ /

Inner Join

|--||||||||--|

| A | B | C | D | D | E | F | G | H |

|--||||||||--|

| a1 | b1 | c1 | 1 | 1 | e1 | f1 | g1 | h1 |

| a2 | b2 | c2 | 1 | 1 | e1 | f1 | g1 | h1 |

| a4 | b4 | c4 | 3 | 3 | e2 | f2 | g2 | h2 |

|--||||||||--|

Example

Output a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that are not sold out:

DATA: DATE LIKE SFLIGHT-FLDATE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID.

SELECT F_{CARRID F}CONNID F~FLDATE

INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID AND

F_{CONNID = P}CONNID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

ENDSELECT.

If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or a table alias.

Note

In order to determine the result of a SELECT command where the FROM clause contains a join, the database system first creates a temporary table containing the lines that meet the ON condition. The WHERE condition is then applied to the temporary table. It does not matter in an inner join whether the condition is in the ON or WHEREclause. The following example returns the same solution as the previous one.

Example

Output of a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that are not sold out:

DATA: DATE LIKE SFLIGHT-FLDATE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID.

SELECT F_{CARRID F}CONNID F~FLDATE

INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID

WHERE F_{CONNID = P}CONNID

AND P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

ENDSELECT.

Note

Since not all of the database systems supported by SAP use the standard syntax for ON conditions, the syntax has been restricted. It only allows those joins that produce the same results on all of the supported database systems:

Only a table or view may appear to the right of the JOIN operator, not another join expression.

Only AND is possible in the ON condition as a logical operator.

Each comparison in the ON condition must contain a field from the right-hand table.

If an outer join occurs in the FROM clause, all the ON conditions must contain at least one "real" JOIN condition (a condition that contains a field from tabref1 amd a field from tabref2.

Note

In some cases, '*' may be specified in the SELECT clause, and an internal table or work area is entered into the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the FROM clause, according to the structure of each table work area. There can then be gaps between table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, not simply by counting the total number of fields. For an example, see below:

plz reward if useful

Re: Inner Join Question

Former Member — Wed, 17 May 2006 12:38:05 GMT

Hi,

NNER JOIN

One or more lines on the right-hand table is linked to a line in the

left-hand table. Lines from the left-handed table

is only selected if they meet the ON criteria.

FROM from vbak as a inner join vbap as b

ON b_{vbeln = a}vbeln.

One or more lines from vbap is selected for each line in vbak.

Limits on the ON clause:

LEFT OUTER JOIN

The left outer join read lines from the left-handed table EVEN if there is

no corresponding line in the right hand table.

FROM vbak as a left outer join vbap as b

ON b_{vbeln = a}vbeln.

If vbap does not contain any lines that meets the condition, a single line

where the values from vbap is filled with null values.

Example 1

SELECT a_{zafstemnr b}zafstemnr b~zsaknr

INTO CORRESPONDING FIELDS OF TABLE i_tab

FROM zcostafstm as a INNER JOIN zcostplan as b

ON b_{zafstemnr = a}zafstemnr.

Example 2

SELECT lips_{vbeln lips}lfimg likp~wadat

INTO corresponding fields of table ltab_orders

FROM lips JOIN likp on ( lips_{vbeln = likp}vbeln )

JOIN vbfa on ( lips_{vbeln = vbfa}vbelv )

WHERE lips~matnr = matnr

and lips~vbeln in vbeln

and likp~vbeln in vbeln

and vbfa~vbelv in vbeln

and lips~werks in werks

and likp~wadat in datum

and vbfa~vbtyp_n = 'Q' "WMS transfer order

and vbfa~vbtyp_v = 'J'. "Delivery

From ABAP hints & Tips: http://oasis.fortunecity.com/skegness/110/JOIN.html

Re: Inner Join Question

Former Member — Wed, 17 May 2006 12:38:32 GMT

Hi,

That is the correct answer actually. There will be no redundancy if you use innerjoin.

For ex: If you join VBAK and VBAP tables there will not be any redundancy. It will fetch exact records.

Thanks,

Surya

Re: Inner Join Question

Former Member — Wed, 17 May 2006 12:39:35 GMT

Your answer is correct, if it is left outer join.

Actually there is ambiguity in the question..

'Redundant data' Whether refers to the record extracted from one table even if there is no record in the other table OR is that extracting same details of 1 table for multiple records of the other table??

Message was edited by: Vijay

Re: Inner Join Question

Former Member — Wed, 17 May 2006 12:46:27 GMT

Maybe it is the terminology they are using. If I have a table with one row (t005n), and join it using the primary keys to a table with many rows (t005o), aren't the records I get back from the first table redundant? n~mandt is returned many times, though there is only 1 row in the left table.

REPORT  zz_download_table.

TYPES: BEGIN OF type_t005o.
        INCLUDE STRUCTURE t005o.
TYPES: END OF type_t005o.

DATA: g_t_t005o TYPE TABLE OF type_t005o.

FIELD-SYMBOLS <fs> TYPE t005o.

START-OF-SELECTION.
  SELECT n~mandt
         n~land1
         n~nameformat
         o~lfdnr
         o~fieldname
         o~value
         o~fieldconv
  INTO CORRESPONDING FIELDS OF TABLE g_t_t005o
  FROM t005n AS n
           INNER JOIN
       t005o AS o
   ON  n~mandt = o~mandt
   AND n~land1 = o~land1
   AND n~nameformat = o~nameformat
   WHERE n~mandt = '010'
   AND n~land1 = 'DE'
   AND n~nameformat = '01'.

  LOOP AT g_t_t005o ASSIGNING <fs>.
    WRITE:/ <fs>-mandt,
            <fs>-land1,
            <fs>-nameformat,
            <fs>-lfdnr.
  ENDLOOP.

010 DE  01 01 
010 DE  01 02 
010 DE  01 03 
010 DE  01 04

Re: Inner Join Question

rahulkavuri — Wed, 17 May 2006 12:50:41 GMT

The answer is B, redundant data is returned only when we use outer joins... no redundant data is displayed during inner join

select scustom~name scustom~city  		                                               sbook~carrid sbook~bookid 
	  into (scustom-name, scustom-city,
	        sbook-bookid, sbook-carrid)
	  from scustom left outer join sbook  
	  on scustom~id    = sbook~customid and 
	        sbook~fldate = '19971015’.                       
    	           write:/ scustom-name,scustom-ity,
		          sbook-carrid,sbook-bookid.  
		clear: scustom,sbook.                                  
    endselect..

In the code above redundant data from sbook is also returned

refer to this thread

Re: Inner Join Question

Former Member — Wed, 18 Mar 2009 19:11:21 GMT

All,

I know this is an old thread, but there is no way the answer to the question is "B". The answer to this question is without a doubt "C: Redundant data from the left-hand table is included.".

From reading the other replies, I can only assume that there was some sort of misunderstanding of the term "Redundant data." In the context of this particular inner join question, redundant data refers to repeated field values and has nothing to do with how many records are returned. When you have a 1:N relationship (e.g. 1 header record may have zero to many line item records; think VBAK and VBAP) clearly you will get repeated field values from the left-hand table (a.k.a. superior table) when there are more than one corresponding records in the right-hand table.

Take a look at this example and ask yourself if the VBAK fields are considered to be redundant (meaning repeated):


VBAK-VBELN     VBAK-ERDAT    VBAK-VBTYP     VBAP-POSNR     VBAP-MATNR
12345          20090101      XYZ             10            MAT123
12345          20090101      XYZ             20            MAT456
12345          20090101      XYZ             30            MAT789

Because there is only 1 VBAK header record in this example but 3 VBAP item records, all of the VBAK header fields are repeated. Therefore, I refer you back to answer "C". Furthermore, since there is redundant data in this 1 to many inner join example (i.e. the repeated VBAK fields) answer "B" is not possible.

By the way, I have seen this exact same question in other sample exams where the given correct answer is that there is redudant data from the left-hand table.

Cheers,

Jamie