05-17-2006 1:32 PM
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?
05-17-2006 1:36 PM
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
05-17-2006 1:37 PM
hi
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
A | B | C | D | D | E | F | G | H |
a1 | b1 | c1 | 1 | 1 | e1 | f1 | g1 | h1 |
a2 | b2 | c2 | 1 | 3 | e2 | f2 | g2 | h2 |
a3 | b3 | c3 | 2 | 4 | e3 | f3 | g3 | h3 |
a4 | b4 | c4 | 3 |
|--|||--|
\ /
\ /
\ /
\ /
\/
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 FCARRID FCONNID F~FLDATE
INTO (CARRID, CONNID, DATE)
FROM SFLIGHT AS F INNER JOIN SPFLI AS P
ON FCARRID = PCARRID AND
FCONNID = PCONNID
WHERE P~CITYFROM = 'FRANKFURT'
AND P~CITYTO = 'NEW YORK'
AND F~FLDATE BETWEEN '20010910' AND '20010920'
AND FSEATSOCC < FSEATSMAX.
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 FCARRID FCONNID F~FLDATE
INTO (CARRID, CONNID, DATE)
FROM SFLIGHT AS F INNER JOIN SPFLI AS P
ON FCARRID = PCARRID
WHERE FCONNID = PCONNID
AND P~CITYFROM = 'FRANKFURT'
AND P~CITYTO = 'NEW YORK'
AND F~FLDATE BETWEEN '20010910' AND '20010920'
AND FSEATSOCC < FSEATSMAX.
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
05-17-2006 1:38 PM
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 bvbeln = avbeln.
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 bvbeln = avbeln.
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 azafstemnr bzafstemnr b~zsaknr
INTO CORRESPONDING FIELDS OF TABLE i_tab
FROM zcostafstm as a INNER JOIN zcostplan as b
ON bzafstemnr = azafstemnr.
Example 2
SELECT lipsvbeln lipslfimg likp~wadat
INTO corresponding fields of table ltab_orders
FROM lips JOIN likp on ( lipsvbeln = likpvbeln )
JOIN vbfa on ( lipsvbeln = vbfavbelv )
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
05-17-2006 1:46 PM
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
05-17-2006 1:38 PM
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
05-17-2006 1:39 PM
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
05-17-2006 1:50 PM
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
03-18-2009 7:11 PM
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