Is this SQL Statement correct?

[Former Member]

Hi All,

I want to avoid a subquery and created an INNER JOIN, could anyone tell me whether they are the same or different from the given below SQL statment?. The internal table is already defined etc.

Inner Join

SELECT a_{begda a}endda a_{objid a}sobid b~sobid

FROM hrp1001 AS a

INNER JOIN hrp1001 AS b ON a_{objid = b}objid AND

b~subty = 'A008' AND

b~sclas = 'P'

INTO TABLE itab_allper

WHERE a_{subty = 'A003' AND a}sclas = 'O' and a~endda = '99991231'.

Equivalent in Subquery

SELECT a_{begda a}endda a_{objid a}sobid b~sobid INTO TABLE itab_allper

FROM hrp1001 as a

WHERE a~subty = 'A003' and

a~sclas = 'O' and

a~objid in (SELECT objid FROM HRP1001 as b

WHERE b~subty = 'A008' and

b~sclas = 'O').

Also if I want to display all columns based on objid that are not matching from table b, should I put LEFT OUTTER JOIN instead of INNER JOIN.

Thanks in advance.

Alex.

[Former Member]

Hi Alex Arthur Sam...

Just follow this Inner join syntax

SELECT FLD1 FLD2 FLD3 FLD4 FLD5 INTO CORRESPONDING FIELDS OF TABLE ITAB FROM TABLE1 INNER JOIN TABLE2 ON

TABLE1_{FLD1 = TABLE2}FLD1 INNER JOIN TABLE3 ON TABLE2_{FLD2 = TABLE3}FLD2 INNER JOIN TABL4 ON TABLE3_{FLD3 = TABLE4}FLD3

WHERE FLD1 = 'AA'

Hope this may solve your problem.

Please reward points if found useful.

Thanks and regards,

Rajeshwar.

[Former Member]

Hi Alex,

SELECT a_{begda a}endda a_{objid a}sobid b~sobid

FROM hrp1001 AS a

INNER JOIN hrp1001 AS b ON a_{objid = b}objid AND

b~subty = 'A008' AND

b~sclas = 'P'

INTO TABLE itab_allper

WHERE a_{subty = 'A003' AND a}sclas = 'O' and a~endda = '99991231'.

In the above query,

b~subty = 'A008' AND

b~sclas = 'P' is not the JOIN condition. So, it would be better if you put those conditions in the WHERE clause, instead, as shown below.

SELECT a_{begda a}endda a_{objid a}sobid b~sobid

FROM hrp1001 AS a

INNER JOIN hrp1001 AS b ON a_{objid = b}objid

INTO TABLE itab_allper

WHERE a~subty = 'A003' and

a~sclas = 'O' and

a~endda = '99991231' and

b~subty = 'A008' and

b~sclas = 'P'.

Also ur interpretaion regarding the LEFT OUTER JOIN is correct. In case u want to display all columns based on objid that are not matching from table b, u should use LEFT OUTER JOIN .

Don't forget to reward points, if this is useful.

Pavan.

[Former Member]

Thanks for your reply.