join query

[Former Member]

hi gurus,

i have to create a query with following condition.

if ansvx = '8'

then selection of pernr take place in org.mag using the table hrp1001

(parameter:otype='p' objid=iv_pernr relat = '008' begda<=iv_date

endda =iv_date and sclas = 's')

and hrp9105

(parameter otype='s' objid= hrp1001-sobid, begda <=iv_date and

endda=iv_date

thanks help me the same as soon as possible

it urgent.

for join commom fields are otype and objid

thanks

anuj

point ll be awarde for each and evry helpful answers

Message was edited by:

anuj anuj

[Former Member]

Hi

see the sample code for fetching data from HRP1001 and do accordingly by changing the fields

report zporgr0040

line-size 108

line-count 60(1)

no standard page heading

message-id zndc.

***********************************************************************

• Database Tables

***********************************************************************

tables: hrp1001, " HR Master Record (Orgn. Assignment)

pa0002, " Personal Data

t528t, " Position Texts

t527x, " Org Unit Text

t528b, " Positions

csks, " Cost Center

cskt. " Cost Center Texts

***********************************************************************

• Declaration of Internal Tables

***********************************************************************

• Internal Table for Orgn Object Data HRP1000

data: begin of obj_tab occurs 0,

otype like hrp1000-otype, " Object Type

objid like hrp1000-objid, " Object ID

plvar like hrp1000-plvar, " Plan Version

istat like hrp1000-istat, " Planning Status

begda like hrp1000-begda, " Begin date

endda like hrp1000-endda, " End date

end of obj_tab.

• Internal Table for Active Employees data PA0000

data: begin of stat_tab occurs 0,

pernr like pa0000-pernr, " Object Type

begda like pa0000-begda, " Begin date

endda like pa0000-endda, " End date

stat2 like pa0000-stat2, " Employment Status

end of stat_tab.

• Internal Table for Actual Orgn Assignment Data PA0001

data: begin of act_tab occurs 0,

pernr like pa0001-pernr, " Object Type

begda like pa0001-begda, " Begin date

endda like pa0001-endda, " End date

kostl like pa0001-kostl, " Cost Center

orgeh like pa0001-orgeh, " Org Unit

plans like pa0001-plans, " Position

natio like pa0002-natio, " Nationality

end of act_tab.

• Internal Table for Orgn Data HRP1001

data: begin of org_tab occurs 0,

otype like hrp1001-otype, " Object Type

objid like hrp1001-objid, " Object ID

plvar like hrp1001-plvar, " Plan Version

istat like hrp1001-istat, " Planning Status

begda like hrp1001-begda, " Begin date

endda like hrp1001-endda, " End date

subty like hrp1001-subty, " Sub Type

sclas like hrp1001-sclas, " Type of Related Object

sobid like hrp1001-sobid, " Id of Related Object

kostl like pa0001-kostl, " Cost Center

plstx like t528t-plstx, " Position Text

orgtx like t527x-orgtx, " Org Unit Text

end of org_tab.

• Internal Table for Position Calculations

data: begin of org1_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

objid like hrp1001-objid, " Object ID

end of org1_tab.

• Internal Table for Position Calculations

data: begin of org2_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

count type i, " No of Positions

end of org2_tab.

• Internal Table for Actual Manpower Calculations

data: begin of act1_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

natio like pa0002-natio, " Nationality

plans like pa0001-plans, " Position

end of act1_tab.

• Internal Table for Actual Manpower Calculations(UAE)

data: begin of act2_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

natio like pa0002-natio, " Nationality

end of act2_tab.

• Internal Table for Actual Manpower Calculations(Others)

data: begin of act3_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

natio like pa0002-natio, " Nationality

end of act3_tab.

• Internal Table for Actual Manpower Calculations(UAE)

data: begin of act4_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

count1 type i, " No of Positions(UAE)

end of act4_tab.

• Internal Table for Actual Manpower Calculations(OTHERS)

data: begin of act5_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

count2 type i, " No of Positions(Others)

end of act5_tab.

• Internal Table for Vaccancies Data

data: begin of vac_tab occurs 0,

plvar like hrp1007-plvar, " Plan Version

otype like hrp1007-otype, " Object Type

objid like hrp1007-objid, " Object ID

istat like hrp1007-istat, " Planning Status

begda like hrp1007-begda, " Begin date

endda like hrp1007-endda, " End date

vacan like hrp1007-vacan, " Vacancy Indicator

status like hrp1007-status, " Status of Vacancy

end of vac_tab.

• Internal Table to store the Report Output data

data : begin of rep_tab occurs 0 ,

kostl(10) type n, " Cost Cente

plans like pa0001-plans, " Position

vacan like hrp1007-vacan, " Vacancy Indicator

status like hrp1007-status, " Status of Vacancy

plstx like t528t-plstx, " Position Text

orgtx like t527x-orgtx, " Org Unit Text

end of rep_tab.

• Internal Table to store the Report Output data

data : begin of rep1_tab occurs 0 ,

kostl(10) type n, " Cost Cente

cnt type i, " No of Vacancies

end of rep1_tab.

• Internal Table to store the Report Output data

data : begin of rep2_tab occurs 0 ,

kostl(10) type n, " Cost Cente

app_no type i, " Approved

nat_no type i, " National

exp_no type i, " Expatriate

tot_no type i, " Total

vac_no type i, " Vacancies

end of rep2_tab.

***********************************************************************

• Declaration of Variables

***********************************************************************

data: v_sobid like hrp1001-sobid, " Object Id

v_sobid1 like hrp1001-sobid, " Object Id

v_sobid2 like hrp1001-sobid, " Object Id

v_sclas like hrp1001-sclas, " Related Obj Type

v_subty like hrp1001-subty, " Related Object Id

v_otype like hrp1007-otype, " Object Type

• v_str(9) type c, " Text

• v_str1(10) type c, " Text

v_cnt like sy-tabix. " Table Index

***********************************************************************

• Declaration of Constants

***********************************************************************

constants : c_x type c value 'X', " Flag

c_stat1 like pa0000-stat2 value '3', " Emp Status

c_plan like hrp1001-plvar value '01', " Plan Version

c_name like hrp1000-uname value 'SAP', " Uname

c_uae like pa0002-natio value 'AE', " Country

c_type like hrp1007-otype value 'O', " Object Type

c_otype like hrp1007-otype value 'S', " Object Type

c_stat like hrp1007-status value '0', " Status

c_pstat like hrp1007-istat value '1', " Plang Status

c_sclas like hrp1001-sclas value 'K', " Rel. Obj Type

c_subty like hrp1001-subty value 'A002', " Related ObjId

c_subty2 like hrp1001-subty value 'A003', " Related ObjId

c_sclas1 like hrp1001-sclas value 'O', " Rel.Obj Type

c_subty1 like hrp1001-subty value 'A011', " Rel.Object Id

c_kokrs like coej-kokrs value '1000'. " Controlling Area

***********************************************************************

• Selection Screen

***********************************************************************

selection-screen begin of block b1 with frame title text-001.

parameters :

p_date like pa0001-begda obligatory default sy-datum. " Date

select-options :

s_kostl for csks-kostl, " Cost Center

s_plans for t528b-plans. " Position ID

selection-screen end of block b1.

***********************************************************************

• At selection-screen

***********************************************************************

at selection-screen.

• Validate the Selection Screen fields

perform validate_screen.

***********************************************************************

• Start-of-Selection

***********************************************************************

start-of-selection.

• Get the Orgn data from database Table HRP1001

perform get_org_data.

• Get the Actual Manpower from PA0001

perform get_act_data.

• Get the Vaccancies data from database Table HRP1007

perform get_vac_data.

• Append the data into final Internal Table

perform append_data.

***********************************************************************

• Top-of-page

***********************************************************************

top-of-page.

• Write the Report and Column Headings

perform top_of_page.

***********************************************************************

• End-of-Page

***********************************************************************

end-of-page.

write /1(108) sy-uline.

***********************************************************************

• End-of-Selection

***********************************************************************

end-of-selection.

• Display the Output Report.

perform display_report.

***********************************************************************

• Form-Routines

***********************************************************************

&----

---

*& Form validate_screen

&----

---

• Validation of selection Screen fields

----

---

form validate_screen.

• Validation of Position ID

clear t528b.

if not s_plans[] is initial.

select single plans

into t528b-plans

from t528b

where plans in s_plans.

if sy-subrc <> 0.

message e999 with 'Invalid Position ID'(009).

endif.

endif.

• Validation of Cost Center

clear csks.

if not s_kostl[] is initial.

select single kostl

into csks-kostl

from csks

where kostl in s_kostl.

if sy-subrc <> 0.

message e999 with 'Invalid Cost Center'(008).

endif.

endif.

endform. "validate_screen

&----

---

*& Form get_org_data

&----

---

• Get the Orgn Data from Database Table HRP1001

*----

---

form get_org_data.

• v_str = 'Developee'(002).

• v_str1 = 'Deployment'(003).

• Select All the Positions from HRP1000

select otype " Object Type

objid " Object ID

plvar " Plan Version

istat " Planning Status

begda " Begin date

endda " End date

into table obj_tab

from hrp1000

where otype = c_otype and

plvar = c_plan and

istat = c_pstat and

begda le p_date and

endda ge p_date and

objid in s_plans and

uname ne c_name.

sort obj_tab by otype objid.

• Get the Orgn and Cost Center for all the above Positions

if not obj_tab[] is initial.

select otype " Object Type

objid " Object ID

plvar " Plan Version

istat " Planning Status

begda " Begin date

endda " End date

subty " Sub Type

sclas " Type of Related Object

sobid " Id of Related Object

into table org_tab

from hrp1001

for all entries in obj_tab

where otype = c_otype and

plvar = c_plan and

istat = c_pstat and

begda le p_date and

endda ge p_date and

sclas eq c_sclas1 and

subty eq c_subty2 and

objid eq obj_tab-objid.

endif.

sort org_tab by otype objid.

• Get the Cost Center

loop at org_tab.

v_otype = c_otype. " S

v_sclas = c_sclas. " K

v_subty = c_subty1. " A011

v_sobid = org_tab-objid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc = 0.

org_tab-kostl = hrp1001-sobid+0(10).

else.

v_otype = c_otype. " S

v_sclas = c_sclas1. " O

v_subty = c_subty2. " A003

v_sobid = org_tab-objid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc = 0.

v_otype = c_type. " O

v_sclas = c_sclas. " K

v_subty = c_subty1. " A011

v_sobid1 = v_sobid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc <> 0.

v_otype = c_type. " O

v_sclas = c_sclas1. " O

v_subty = c_subty. " A002

v_sobid = v_sobid1.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc = 0.

v_otype = c_type. " O

v_sclas = c_sclas. " K

v_subty = c_subty1. " A011

v_sobid2 = v_sobid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc <> 0.

v_otype = c_type. " O

v_sclas = c_sclas1. " O

v_subty = c_subty. " A002

v_sobid = v_sobid2.

perform get_org using v_otype v_sclas v_subty

v_sobid.

else.

org_tab-kostl = hrp1001-sobid+0(10).

endif.

endif.

else.

org_tab-kostl = hrp1001-sobid+0(10).

endif.

else.

v_otype = c_otype. " S

v_sclas = c_otype. " S

v_subty = c_subty. " A002

v_sobid = org_tab-objid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc = 0.

v_otype = c_otype. " S

v_sclas = c_sclas. " K

v_subty = c_subty1. " A011

v_sobid1 = v_sobid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc <> 0.

v_otype = c_otype. " S

v_sclas = c_sclas1. " O

v_subty = c_subty2. " A003

v_sobid = v_sobid1.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc = 0.

v_otype = c_type. " O

v_sclas = c_sclas. " K

v_subty = c_subty1. " A011

v_sobid2 = v_sobid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc <> 0.

v_otype = c_type. " O

v_sclas = c_sclas1. " O

v_subty = c_subty. " A002

v_sobid = v_sobid2.

perform get_org using v_otype v_sclas v_subty

v_sobid.

else.

org_tab-kostl = hrp1001-sobid+0(10).

endif.

endif.

else.

org_tab-kostl = hrp1001-sobid+0(10).

endif.

endif.

endif.

endif.

• Get the Position Text

clear t528t.

select single plstx from t528t into t528t-plstx

where sprsl = sy-langu and

otype = c_otype and

plans = org_tab-objid.

org_tab-plstx = t528t-plstx.

• Get the Org Unit Text

clear t527x.

select single orgtx from t527x into t527x-orgtx

where sprsl = sy-langu and

orgeh = org_tab-sobid+0(8).

org_tab-orgtx = t527x-orgtx.

modify org_tab index sy-tabix.

clear v_sobid.

endloop.

sort org_tab by otype objid.

• Calculate the Approved No of Positions

loop at org_tab.

if org_tab-kostl in s_kostl.

move-corresponding org_tab to org1_tab.

append org1_tab.

clear org1_tab.

endif.

endloop.

sort org1_tab by kostl objid.

clear v_cnt.

loop at org1_tab.

at end of kostl.

sy-tabix = sy-tabix - v_cnt.

org2_tab-kostl = org1_tab-kostl.

org2_tab-count = sy-tabix.

v_cnt = v_cnt + sy-tabix.

append org2_tab.

clear:org2_tab.

endat.

endloop.

sort org2_tab by kostl.

endform. "get_org_data

&----

---

*& Form get_act_data

&----

---

• Get the Actual Manpower Data from Database Table PA0001

*----

---

form get_act_data.

clear : stat_tab, act_tab.

refresh: stat_tab, act_tab.

• Get all the Active Employees

select pernr " Object Type

begda " Begin date

endda " End date

stat2 " Emp Status

into table stat_tab

from pa0000

where stat2 eq c_stat1 and

begda le p_date and

endda ge p_date.

sort act_tab by pernr.

if not stat_tab[] is initial.

select pernr " Object Type

begda " Begin date

endda " End date

kostl " Cost Center

orgeh " Org Unit

plans " Position

into table act_tab

from pa0001

for all entries in stat_tab

where pernr eq stat_tab-pernr and

kostl in s_kostl and

plans in s_plans and

persg eq '1' and

begda le p_date and

endda ge p_date.

endif.

sort act_tab by pernr.

loop at act_tab.

move-corresponding act_tab to act1_tab.

clear pa0002-natio.

select single natio into pa0002-natio from pa0002

where pernr = act_tab-pernr.

if sy-subrc = 0.

act1_tab-natio = pa0002-natio.

endif.

append act1_tab.

clear act1_tab.

endloop.

sort act1_tab by kostl natio.

• Calculate the Local and Expatriate Manpower

loop at act1_tab.

if act1_tab-natio = c_uae.

act2_tab-kostl = act1_tab-kostl.

act2_tab-natio = act1_tab-natio.

else.

act3_tab-kostl = act1_tab-kostl.

act3_tab-natio = act1_tab-natio.

endif.

append: act2_tab, act3_tab.

clear : act2_tab,act3_tab.

endloop.

sort act2_tab by kostl.

sort act3_tab by kostl.

• Actual UAE Manpower

clear v_cnt.

loop at act2_tab.

at end of kostl.

sy-tabix = sy-tabix - v_cnt.

act4_tab-kostl = act2_tab-kostl.

act4_tab-count1 = sy-tabix.

v_cnt = v_cnt + sy-tabix.

append act4_tab.

clear:act4_tab.

endat.

endloop.

sort act4_tab by kostl.

• Other Countries

clear v_cnt.

loop at act3_tab.

at end of kostl.

sy-tabix = sy-tabix - v_cnt.

act5_tab-kostl = act3_tab-kostl.

act5_tab-count2 = sy-tabix.

v_cnt = v_cnt + sy-tabix.

append act5_tab.

clear:act5_tab.

endat.

endloop.

sort act5_tab by kostl.

endform. "get_act_data

&----

---

*& Form get_org

&----

---

• Get the Cost Center from Parent Org Id

----

---

form get_org using p_v_otype p_v_sclas p_v_subty p_v_sobid.

clear hrp1001.

select single * from hrp1001

where otype = p_v_otype and

plvar = c_plan and

istat = c_pstat and

begda le p_date and

endda ge p_date and

sclas eq p_v_sclas and

subty eq p_v_subty and

objid eq p_v_sobid.

v_sobid = hrp1001-sobid+0(8).

endform. " get_org

&----

---

*& Form get_vac_data

&----

---

• Get the Vaccancies Data from Database Table HRP1007

----

---

form get_vac_data.

if not org_tab[] is initial.

select plvar " Plan Version

otype " Object Type

objid " Object ID

istat " Planning Status

begda " Begin date

endda " End date

vacan " Vacancy Indicator

status " Status of Vacancy

into table vac_tab

from hrp1007

for all entries in org_tab

where plvar = c_plan and

otype = c_otype and

istat eq c_pstat and

objid = org_tab-objid and

begda le p_date and

endda ge p_date and

vacan eq c_x and

status eq c_stat.

endif.

sort vac_tab by plvar otype objid.

• Get the HR data into the rep_tab Internal Table

loop at vac_tab.

rep_tab-plans = vac_tab-objid.

rep_tab-vacan = vac_tab-vacan.

rep_tab-status = vac_tab-status.

read table org_tab with key objid = vac_tab-objid.

if sy-subrc = 0.

rep_tab-kostl = org_tab-kostl.

rep_tab-plstx = org_tab-plstx.

rep_tab-orgtx = org_tab-orgtx.

endif.

append rep_tab.

clear rep_tab.

endloop.

loop at rep_tab.

if not rep_tab-kostl in s_kostl.

delete rep_tab index sy-tabix.

endif.

endloop.

sort rep_tab by kostl plans.

• delete rep_tab where plstx cs v_str.

• delete rep_tab where orgtx cs v_str1.

• Calculate the Count of Vacancies in each Division

clear v_cnt.

loop at rep_tab.

at end of kostl.

sy-tabix = sy-tabix - v_cnt.

rep1_tab-kostl = rep_tab-kostl.

rep1_tab-cnt = sy-tabix.

v_cnt = v_cnt + sy-tabix.

append rep1_tab.

clear:rep1_tab.

endat.

endloop.

sort rep1_tab by kostl.

endform. "get_vac_data

&----

---

*& Form append_data

&----

---

• Append the data into final Internal Table

----

---

form append_data.

loop at org2_tab.

rep2_tab-kostl = org2_tab-kostl.

rep2_tab-app_no = org2_tab-count.

read table act4_tab with key kostl = org2_tab-kostl

binary search.

if sy-subrc = 0.

rep2_tab-nat_no = act4_tab-count1.

endif.

read table act5_tab with key kostl = org2_tab-kostl

binary search.

if sy-subrc = 0.

rep2_tab-exp_no = act5_tab-count2.

endif.

read table rep1_tab with key kostl = org2_tab-kostl

binary search.

if sy-subrc = 0.

rep2_tab-vac_no = rep1_tab-cnt.

endif.

rep2_tab-tot_no = rep2_tab-nat_no + rep2_tab-exp_no.

append rep2_tab.

clear rep2_tab.

endloop.

sort rep2_tab by kostl.

endform. "append_data

&----

---

*& Form top_of_page

&----

---

• Write the Report and Column Headings

----

---

form top_of_page.

skip.

format color col_heading on.

write: /1(108) 'NATIONAL DRILLING COMPANY'(010) centered,

/1(108) 'Summary of Manning Level Report'(011) centered.

format color off.

skip.

write: /2 'Key Date :'(013), p_date,

75 'Report Run Date:'(041), sy-datum.

if not s_kostl[] is initial.

if s_kostl-high is initial.

write: /2 'Cost Center :'(023), s_kostl-low,

75 'Time :'(042), sy-uzeit.

else.

write: /2 'Cost Center From:'(024), s_kostl-low+7(3),

'To'(022), s_kostl-high,

75 'Time :'(042), sy-uzeit.

endif.

else.

write: /75 'Time :'(042), sy-uzeit.

endif.

if not s_plans[] is initial.

if s_plans-high is initial.

write: /2 'Position Id :'(019), s_plans-low,

75 'User :'(043), sy-uname.

else.

write: /2 'Position Id From:'(021), s_plans-low,

'To'(022), s_plans-high,

75 'User :'(043), sy-uname.

endif.

else.

write: /75 'User :'(043), sy-uname.

endif.

write: /75 'Page No :'(044), sy-pagno.

skip.

write /1(108) sy-uline.

format color col_heading.

write:/1 sy-vline,

12 sy-vline, 53 sy-vline,

64 sy-vline, 65(32) 'Actual'(018) centered,

97 sy-vline,108 sy-vline.

write:/1 sy-vline, 2(10) 'CostCenter'(014),

12 sy-vline, 13(40) 'Description'(026) centered,

53 sy-vline, 54(10) 'Approved'(025) centered,

64 sy-vline, 65(32) sy-uline,

97 sy-vline, 98(10) 'Vacancies'(017),

108 sy-vline.

write:/1 sy-vline,

12 sy-vline, 53 sy-vline,

64 sy-vline, 65(10) 'National'(015) centered,

75 sy-vline, 76(10) 'Expatriate'(020) centered,

86 sy-vline, 87(10) 'Total'(016) centered,

97 sy-vline,108 sy-vline.

format color off.

write /1(108) sy-uline.

endform. "top_of_page

&----

---

*& Form Display_report

&----

---

• Write the Report Output

----

---

form display_report.

if rep2_tab[] is initial.

message i999 with

'No Data found for the entered Selection'(035).

else.

loop at rep2_tab.

• Authorization Check for the Orgn and Cost Centers

perform auth_check_org.

if sy-subrc = 0.

clear cskt.

select single ltext into cskt-ltext from cskt

where spras = sy-langu and

kokrs = c_kokrs and

kostl = rep2_tab-kostl.

format color col_normal.

write: /1 sy-vline, 2(10) rep2_tab-kostl+7(3),

12 sy-vline, 13(40) cskt-ltext,

53 sy-vline, 54(10) rep2_tab-app_no,

64 sy-vline, 65(10) rep2_tab-nat_no,

75 sy-vline, 76(10) rep2_tab-exp_no,

86 sy-vline, 87(10) rep2_tab-tot_no,

97 sy-vline, 98(10) rep2_tab-vac_no,

108 sy-vline.

format color off.

at last.

sum.

format color 3.

write /1(108) sy-uline.

write: /1 sy-vline, 2(10) 'Total'(027),

12 sy-vline,

53 sy-vline, 54(10) rep2_tab-app_no,

64 sy-vline, 65(10) rep2_tab-nat_no,

75 sy-vline, 76(10) rep2_tab-exp_no,

86 sy-vline, 87(10) rep2_tab-tot_no,

97 sy-vline, 98(10) rep2_tab-vac_no,

108 sy-vline.

format color off.

endat.

endif.

endloop.

endif.

write /1(108) sy-uline.

endform. " Display_report

&----

---

*& Form auth_check_org

&----

---

• Authorization Check for the Orgn and Cost Center

*----

---

form auth_check_org.

authority-check object 'ZNDCHROM'

id 'PLVAR' dummy

id 'OTYPE' dummy

id 'KOSTL' field rep2_tab-kostl.

endform. "auth_check_org

Reward points for useful Answers

Regards

Anji

[Former Member]

hi

good

go through this which ll give you detail about the join query,

When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network.

Let us take an example of 2 tables, zairln and zflight. The table zairln has the field airln, which is the airline code and the field lnnam, which is the name of the airline. The table zflight has the field airln, the airline code and other fields which hold the details of the flights that an airline operates.

Since these 2 tables a re logically joined by the airln field, it is advisable to use the inner join.

Select a_{airln a}lnnam b_{fligh b}cntry into table int_airdet

From zairln as a inner join zflight as b on a_{airln = b}airln.

In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join.

thanks

mrutyun^