topic Re: JOINS in Application Development and Automation Discussions

JOINS

Former Member — Fri, 14 Sep 2007 05:47:18 GMT

Hi,

Can anybody please explain the following, if possible with an example:

1. None of the fields in the table on the right can apear in the WHERE conditions of the LEFT OUTER JOIN.

2. SELECT <fieldlist> INTO <target>

FROM <dbtab1> AS <alias1>

INNER JOIN <dbtab2> AS <alias2>

ON <alaias1>_{<dbtab1-field1> = <alials2>}<dbtab2-field1>.

Please explain the about code specially the alaising involved.

Regards,

Dhiraj Mehta

Re: JOINS

Former Member — Fri, 14 Sep 2007 05:58:31 GMT

Hi dhiraj,

for outer joins go thro this document.

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

2) it is just giving another name to the dbtable in the select query.

take an example:

select a_{matnr b}werks into corresponding fields of table itab from mara as a inner join marc as b on a_{matnr = b}matnr.

Regards....

Arun.

Reward points if useful.

Re: JOINS

Former Member — Fri, 14 Sep 2007 06:03:19 GMT

Hi Dhiraj

I have attached a sample code for your reference.

SELECT a_{aufnr a}auart a_{erdat a}aenam a~ktext

a_{kostv a}kostl a_{objnr a}kdauf a~kdpos

a_{adrnra a}pspel a_{ernam a}zzact_cde

b_{priok b}equnr b_{bautl b}iloan

b_{gewrk b}addat b_{qmnum b}ilart

c_{arbpl c}werks

d_{gltrp d}gstrp d_{gluzp d}gsuzp d_{maufnr d}aufpl

e_{tplnr e}abckz e_{eqfnr e}beber e~proid

f~bstkd

g_{groes g}inbdt g_{eqart g}eqtyp

i~ktsch

INTO TABLE i_order

FROM aufk AS a INNER JOIN afih AS b ON a_{aufnr = b}aufnr

INNER JOIN crhd AS c ON c_{objid = b}gewrk

INNER JOIN afko AS d ON a_{aufnr = d}aufnr

INNER JOIN iloa AS e ON b_{iloan = e}iloan

INNER JOIN pmsdo AS f ON f_{objnr = a}objnr

LEFT OUTER JOIN equi AS g ON g_{equnr = b}equnr

INNER JOIN afvc AS i ON i_{aufpl = d}aufpl

WHERE a~aufnr IN s_aufnr

AND a~auart IN p_auart

AND a~kostv IN s_kostv

AND a~kdauf IN s_kdauf

AND b~ilart IN s_ilart

AND b~equnr IN s_equi

AND b~addat IN s_period

AND b~iphas IN r_iphas

AND b~priok IN s_proik

AND c~arbpl IN s_arbpl

AND c~werks IN s_werks

AND d~maufnr IN s_maufnr

AND d~gstrp IN s_gstrp

AND d~gltrp IN s_gltrp

AND e~beber IN s_beber

AND e~stort IN s_stort

AND e~tplnr IN s_tplnr

AND f~bstkd IN s_bstkd

AND i~loekz NE 'X'.

You can see that I have used so many inner joins.

The method of alaising comes when you have two tables with the same fieldname and you have distinguish between them.

For example in this sample code you have AUFNR in AUFK and AFIH table as well. But we are interested to pick only AUFNR from AUFK table and not from AFIH.

Therefore we write a~aufk in the select query < field list>.

I have also used the left outer join. But u can check that I have not used the fileds of the left outer join table equi. That is the way the syntax of a select query using joins are defined.

Similarly you can verify the other fields in the select query.

Hope its clear now.

Please award points if the answer is satisfactory.

Regards,

Bhanu

Re: JOINS

Former Member — Fri, 14 Sep 2007 06:12:06 GMT

Hi dhiraj

1. No you can't , For outter join , you need the same field between two table that you join but there is no need for the same data between there.

2. SELECT <fieldlist> INTO <target> FROM <dbtab1> AS <alias1>

meaning is Selecting data of field from table and keep into variable and name the table as a.

example

select bukrs into v_bukrs from bsid as a

bukrs is field in table

v_bukrs is variable that you declare

you name the table as a , next time you can mention this table as a .

SELECT <fieldlist> INTO <target>

FROM <dbtab1> AS <alias1>

INNER JOIN <dbtab2> AS <alias2>

ON <alaias1>_{<dbtab1-field1> = <alials2>}<dbtab2-field1>.

you have two table and two table have relationship , you select the data from two table by condition that link between two table.

Example

select a.bukrs b.xxx into (v_bukrs,v_xxx)

from bsid as a

inner join bsad as b

on a_{mandt = b}mandt

you select data in field bukrs from table bsid and xxx from table bsad and keep into variable v_bukrs and v_xxx which condition that field mandt in the two table are the same.

Regards

Wiboon