what is difference procrdure for making foreign key & inner and outer join?

[Former Member]

hi

I am shabeer ahmed

i want to know procedure of how to make create foreign key and innerjoin and outer join.

[Sm1tje]

When creating your own custom table in transaction SE11, you can define a foreign key relationship for one (or more) columns / fields of table base table. Mark the field / column for which you want to do this, and click on the button with the key at the top of your table definition (table control).

Make sure that the datatype of your column has the same data type as the field from the table for which the foreign key has to be assigned. For example take table T001 and T001D.

In table T001 all valid company codes (BUKRS) are stored. In table T001D field BUKRS is also available. When you mark this field and click on the button with the key, you'll see a pop-up on which the foreign key relationship is displayed.

So when you create your own foreign. this pop-up will also be shown, but it's empty. When the datatype is the same as the datatype from the foreign key table, SAP will make a proposal. If the datatype differs, you will have to enter the name of your check table yourself and then click the button [GENERATE PROPOSAL].

Inner join:

Join the columns carrname, connid, fldate of the database tables scarr, spfli and sflight by means of two inner joins. A list is created of the flights from p_cityfr to p_cityto. Alternative names are used for every table.

PARAMETERS: p_cityfr TYPE spfli-cityfrom,

p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,

fldate TYPE sflight-fldate,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY fldate carrname connid.

SELECT c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

LOOP AT itab INTO wa.

WRITE: / wa-fldate, wa-carrname, wa-connid.

ENDLOOP.

Outer join:

Join the columns carrid, carrname and connid of the database tables scarr and spfli using an outer join. The column connid is set to the null value for all flights that do not fly from p_cityfr. This null value is then converted to the appropriate initial value when it is transferred to the assigned data object. The LOOP returns all airlines that do not fly from p_cityfr.

PARAMETERS p_cityfr TYPE spfli-cityfrom.

DATA: BEGIN OF wa,

carrid TYPE scarr-carrid,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH NON-UNIQUE KEY carrid.

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.

LOOP AT itab INTO wa.

IF wa-connid = '0000'.

WRITE: / wa-carrid, wa-carrname.

ENDIF.

ENDLOOP.

For inner and outer join, please first go to transaction SE38, click on the info button and enter your statement. It will give you exactly what you want.....

Good luck to you.