topic Re: JoinS in Application Development and Automation Discussions

JoinS

Former Member — Wed, 13 Sep 2006 09:51:20 GMT

Hi..

What are the kind of JOINS that we normally use in a Select Query?

Please explain me by giving a small example of 2 tables to make the picture clear.

Thank you

Re: JoinS

Former Member — Wed, 13 Sep 2006 09:53:22 GMT

Info in help is good to understand better. Below is the

same for you understanding.

... FROM tabref1 [INNER] JOIN tabref2 ON cond 




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 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: 



Variant 3 
... FROM tabref1 LEFT [OUTER] JOIN tabref2 ON cond 




Effect 
Selects the data from the transparent database tables and/or views specified in tabref1 and tabref2. tabref1 und tabref2 both have either the same form as in variant 1 or are themselves join expressions. The keyword OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized by the ABAP-Dictionary. 



In order to determine the result of a SELECT command where the FROM clause contains a left outer join, the database system creates a temporary table containing the lines that meet the ON condition. The remaining fields from the left-hand table (tabref1) are then added to this table, and their corresponding fields from the right-hand table are filled with ZERO values. The system then applies the WHERE condition to the table. 



Left outer join between table 1 and table 2 where column D in both tables set the join condition: 



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  |        |----|----|----|----|----| 
|----|----|----|----| 

                            / 
                           / 
                          / 
                         / 
                        / 
    Left Outer 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 | 
    | a3 | b3 | c3 | 2  |NULL|NULL|NULL|NULL|NULL| 
    | a4 | b4 | c4 | 3  | 3  | e2 | f2 | g2 | h2 | 
    |----|----|----|----|----|----|----|----|----| 



Example 
Output a list of all custimers with their bookings for October 15th, 2001: 



DATA: CUSTOMER TYPE SCUSTOM, 
      BOOKING  TYPE SBOOK. 

SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
       SBOOK~FLDATE SBOOK~CARRID SBOOK~CONNID SBOOK~BOOKID 
       INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY, 
             BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID, 
             BOOKING-BOOKID) 
       FROM SCUSTOM LEFT OUTER JOIN SBOOK 
         ON SCUSTOM~ID = SBOOK~CUSTOMID AND 
            SBOOK~FLDATE = '20011015' 
       ORDER BY SCUSTOM~NAME SBOOK~FLDATE. 
  WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY, 
           BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID, 
           BOOKING-BOOKID. 
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 using an alias. 



Note 
For the resulting set of a SELECT command with a left outer join in the FROM clause, it is generally of crucial importance whether a logical condition is in the ON or WHERE condition. Since not all of the database systems supported by SAP themselves support the standard syntax and semantics of the left outer join, the syntax has been restricted to those cases that return the same solution in all database systems: 



Only a table or view may come after the JOIN operator, not another join statement. 

The only logical operator allowed in the ON condition is AND. 

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

Comparisons in the WHERE condition must not contain a field from the right-hand table. 

The ON condition must contain at least one "real" JOIN condition (a condition in which a field from tabref1 as well as from tabref2 occurs). 



Note 
In some cases, '*' may be specivied as the field list in the SELECT clause, and an internal table or work area is entered in 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 llen in der FROM clause, according to the structure of each table work area. There can be gaps between the 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, as in the following example (not simply by counting the total number of fields). 



Example 
Example of a JOIN with more than two tables: Select all flights from Frankfurt to New York between September 10th and 20th, 2001 where there are available places, and display the name of the airline. 


DATA: BEGIN OF WA, 
        FLIGHT TYPE SFLIGHT, 
        PFLI   TYPE SPFLI, 
        CARR   TYPE SCARR, 
      END OF WA. 

SELECT * INTO WA 
    FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P 
             ON F~CARRID = P~CARRID AND 
                F~CONNID = P~CONNID ) 
           INNER JOIN SCARR AS C 
             ON F~CARRID = C~CARRID 
    WHERE P~CITYFROM = 'FRANKFURT' 
      AND P~CITYTO   = 'NEW YORK' 
      AND F~FLDATE BETWEEN '20010910' AND '20010920' 
      AND F~SEATSOCC < F~SEATSMAX. 
  WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID, 
           WA-FLIGHT-CONNID. 
ENDSELECT.

Kind Regards

Eswar

Re: JoinS

Former Member — Wed, 13 Sep 2006 09:55:33 GMT

http://help.sap.com/saphelp_erp2005/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/frameset.htm

plz reward the point first time if it is useful :-)...

Re: JoinS

Former Member — Wed, 13 Sep 2006 09:58:30 GMT

Hi,

Pls.search the forum for previous replies on this subject.

Anway. you may also check this link..

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm.

Regards

Re: JoinS

Former Member — Wed, 13 Sep 2006 09:58:38 GMT

Joins are of two types i.e. Inner and Outer joins.

Inner Join : Also called equi joins it is widely used in select query to join two tables. It joins the two tables and retrieves the record where the field exists in both the tables. Example...join between mara and vbap.

select vbap_{matnr mara}extwg

into table itab

from vbap inner join mara on vbap_{matnr = mara}matnr.

Here in the above example it joins the tables on material number and would get the required information from both tables where the material exists in mara as well as vbap.

Outer Join : The ones used are LEFT joins where it would fetch the all the data from the left table even if the correspend match does not exist in the other table. Example : We need to list all material from mara as well as fetch the corresponding sales order for it.

SELECT mara_{matnr vbap}vbeln vbap~posnr

into table itab

from mara left join vbap on mara_{matnr = vbap}matnr.

In the above it would fetch all material information from mara irrespective if the order exists or not.

Please note in the above you cannot have where condition for fields from the other table(ex-VBAP).

Message was edited by: Anurag Bankley

Re: JoinS

Former Member — Wed, 13 Sep 2006 09:58:40 GMT

Hi,

joins are of two types

inner join-> joins two tables to get common data

of both table

table A table b

10 a 10 100

20 b 20 200

result of inner join will be

10 a 100

20 b 200

outer join.-> selects all data from table a

table A table b

10 a 10 100

20 b

result of outer join will be

10 a 100

20 b

Regards

Amole

Re: JoinS

anversha_s — Wed, 13 Sep 2006 09:58:43 GMT

hi subhash,

inner join.

*********************

inner join which means you are slecting entries from both tables only if it meets the codition that the secong table's field matches with the first table's field.

ztable_numplant (table)

bill_number

plant_Number

price

ztable_numdate (table )

bill_number

bill_date

eg: SELECT a_{bill_number a}plant_Number b~bill_Date

INTO TABLE int_table

FROM ( ztable_numplant AS a

INNER JOIN

ztable_numdate AS b

ON a_{bill_number = b}bill_number ).

outer join.

***************************

table a table b

empno name empno salary

a sasi a 1000

b xxx b 2000

c yyy

if you made outer join (left /right ) the left table kept as it is the if the condition satisfy the right table entries will fetch else leave it blank

the output will be

a sasi a 1000

b xxx b 2000

c yyy

eg: syntax

SELECT s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid

AND p~cityfrom = p_cityfr.

rgds

anver

Re: JoinS

Former Member — Wed, 13 Sep 2006 09:58:53 GMT

Hi,

Inner join


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.               
	  Write:/ carrid,connid,date.                    
	  Clear: sflight,spfli.
    Endselect.

We get teh records that satisfies the conditions mentioned after ON keyword.

Outer Join or Left outer 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..

We get all the records from table Scustom. If data exists for the records in Sbook, then the fields will be populated else the fields of sbook will be null.

Regards,

Sailaja.

Re: JoinS

anversha_s — Wed, 13 Sep 2006 10:08:22 GMT

hi subhas,

i posetd one earlier.

adding to that , a simple ex which will help u understand more

chk this, very simple ex.

hi,

table emp

empno name

a sasi

b xxx

c yyy

table sal

empno salary

a 1000

b 2000

Inner join

****************

select e_{empno e}name

s~sal

into table int_table

from emp as e

inner join sal as s

e_{empno = s}empno.

if you made inner join between table a and b by emp no

the selection retrives only if the condition satisfy the output will be

a sasi 1000

b xxx 2000

Outer join

*************************

select e_{empno e}name

s~sal into table int_table

from emp as e

LEFT OUTER JOIN sal as s

e_{empno = s}empno.

if you made outer join (left /right ) the left table kept as it is the

if the condition satisfy the right table entries will fetch else leave it blank

the output will be

a sasi a 1000

b xxx b 2000

c yyy

rgds

anver