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

Inner Join problem

Former Member — Tue, 06 Feb 2007 08:14:22 GMT

Hi all

This is my question. But I cant understand why no value is return, is it query problem or any others possible prob?

scenario :

KNA1 contains

kunnr = 550001

KNVV contains

kunnr doesnt have value 550001

Using "inner join", i should get return of all the values in KNA1 that matched the conditions in S_REP regardless whether KNVV is matched. (based on statement below) Am i correct?

SELECT V_{KUNNR K}VKORG K_{VTWEG K}BZIRK V~BAHNS

INTO CORRESPONDING FIELDS OF TABLE IT_REP

FROM KNA1 AS V

INNER JOIN KNVV AS K ON K_{KUNNR = V}KUNNR

WHERE V~BAHNS IN S_REP.

Output : should contains at least one line in table IT_REP but no. Why so?

Thanks for your help.

p/s : is there any other good tutorial on sql join? Wish to explore more on that. Please share if you know any. TQ.

Re: Inner Join problem

Former Member — Tue, 06 Feb 2007 08:17:05 GMT

If u want all the records should be returned regardless of knvv then use

left outer join, not inner join.

Re: Inner Join problem

seshatalpasai_madala — Tue, 06 Feb 2007 08:21:57 GMT

Hi,

In your query first INNER JOIN is performed, then on the resultset the condition you have given in the WHERE caluse is applied. So if you JOIN it self fetches less data then there is a high chance that you get less data. Just try and see how many records you will get with out the WHERE CLAUSE and with the WHERE CLAUSE. IF you want all that data that is there in LEFT TABLE then use OUTER JOIN.

Regards,

Sesh

Re: Inner Join problem

Former Member — Tue, 06 Feb 2007 08:22:11 GMT

no here you only get the value which are matching the condition of knvv as well as s_rep.

better take all the values from kna1 as per your select option in one itab(ikna1).

and take knvv data to other itab using for all entries with the previous itab(ikna1).

regards

shiba dutta

Re: Inner Join problem

Former Member — Tue, 06 Feb 2007 08:24:13 GMT

See the below threads:

These will give you complete information about the JOINs.

Thanks.

Re: Inner Join problem

Former Member — Tue, 06 Feb 2007 08:24:33 GMT

Hi,

For inner join statements, only matching records from both compared tables will be extracted. Hence, if there is no corresponding data in KNVV for the same customer in KNA1, then, the KNA1 record will not also be extracted.

You could check this link for efficient SQL programming:

<a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/5c68a190-0201-0010-f0b0-8ade1a71dc8c">Efficient Database Programming with ABAP</a>

Kind Regards,

Darwin

Re: Inner Join problem

Former Member — Tue, 06 Feb 2007 08:25:15 GMT

Hui,

// left outer Join

Table 1 Table 2

|--|||--|

\ /

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 |

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

// Inner Join

Table 1 Table 2

\ /

Inner Join

Check the above graph if useful

Pls. mark

Re: Inner Join problem

Former Member — Tue, 06 Feb 2007 10:40:25 GMT

Hi,

Change your select statemet like below.

SELECT V~KUNNR

K~VKORG

K~VTWEG

K~BZIRK

V~BAHNS

INTO CORRESPONDING FIELDS OF TABLE IT_REP

FROM KNA1 AS V INNER JOIN

KNVV AS K

ON V_{KUNNR = K}KUNNR

WHERE V~BAHNS IN S_REP.

Regards,

Jayaram...

Re: Inner Join problem

Former Member — Tue, 06 Feb 2007 13:14:33 GMT

Hi Yeoh,

in place 0f inner join use for all entries it for better performance .

declare 2 internal tables it_kna1 and it_kann

step2 : fetch all record bease on the where close and put it into it_kna1 internal table .

loke select KUNNR

LAND1

NAME1

NAME2

ORT01

PSTLZ

into it_kna1

from kna1

where kunnr eq 550001

if sy-subrc eq 0.

select KUNNR

VKORG

VTWEG

SPART into it_knvv

from knvv

for all entries in it_kna1

where kunnr eq it_kna1-kunnr.

if sy-subrc eq 0.

process what you want ..

endif

endif.

if it help reward the points

Ajay

Re: Inner Join problem

Former Member — Wed, 07 Feb 2007 00:48:05 GMT

Hi Jayaram

Is that make a different? I did notice it yesterday. TQ