Inner Join problem

[Former Member]

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.

[Former Member]

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

left outer join, not inner join.

[seshatalpasai_madala]

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

[Former Member]

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

[Former Member]

See the below threads:

These will give you complete information about the JOINs.

Thanks.

[Former Member]

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

[Former Member]

Hui,

• // left outer Join

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 |

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

• // Inner Join

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

Check the above graph if useful

Pls. mark

[Former Member]

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

[Former Member]

Hi Jayaram

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

[Former Member]

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