topic Re: Is this SQL Statement correct? in Application Development and Automation Discussions

Is this SQL Statement correct?

Former Member — Wed, 14 May 2008 16:29:09 GMT

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.

Re: Is this SQL Statement correct?

Former Member — Thu, 15 May 2008 05:50:19 GMT

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.

Re: Is this SQL Statement correct?

Former Member — Thu, 15 May 2008 07:33:26 GMT

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.

Re: Is this SQL Statement correct?

Former Member — Thu, 15 May 2008 16:28:38 GMT

Thanks for your reply.