INNER JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

INNER JOIN

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-10-21 : 18:06:43

I have written the following in a script

FROM MPL_EXPOSURE M
INNER JOIN POLICY P ON M.POLICY_NUMBER = P.POLICY_NUMBER AND M.POLICY_DATE_TIME = P.POLICY_DATE_TIME
INNER JOIN REGISTER R ON M.POLICY_NUMBER = R.POLICY_NUMBER AND M.POLICY_DATE_TIME = R.POLICY_DATE_TIME
INNER JOIN EDIT_LONG_CODE E ON E.TBNAME = 'MPL_EXPOSURE' AND E.NAME = 'SPECIALTY' AND E.CODE = SPECIALTY
INNER JOIN EDIT_LONG_CODE E2 ON E2.TBNAME = 'MPL_EXPOSURE' AND E2.NAME = 'TYPE_ANCILLARY' AND E2.CODE = TYPE_ANCILLARY
INNER JOIN EDIT_SHORT_CODE E3 ON E3.TBNAME = 'MPL_EXPOSURE' AND E3.NAME = 'TYPE_LMT' AND E3.CODE = TYPE_LMT
INNER JOIN EDIT_SHORT_CODE E4 ON E4.TBNAME = 'REGISTER' AND E4.NAME = 'STATUS_1' AND E4.CODE = R.STATUS_1
INNER JOIN EDIT_SHORT_CODE E5 ON E5.TBNAME = 'MPL_EXPOSURE' AND E5.NAME = 'POL_EXP_TYPE' AND E5.CODE = M.POL_EXP_TYPE
INNER JOIN EDIT_SHORT_CODE E6 ON E6.TBNAME = 'MPL_EXPOSURE' AND E6.NAME = 'EXPOSURE_TYPE' AND E6.CODE = M.EXPOSURE_TYPE
LEFT OUTER JOIN TRIBUTE_SPECIALTY_MAPPING T ON E.CODE = T.OPCO_CODE AND T.NAME = 'SPECIALTY'
LEFT OUTER JOIN TRIBUTE_SPECIALTY_MAPPING T1 ON E2.CODE = T1.OPCO_CODE AND T1.NAME = 'TYPE_ANCILLARY'
INNER JOIN CLIENT C ON M.CLIENT_NUMBER = C.CLIENT_NUMBER
LEFT OUTER JOIN PHX_NOVA_CONTACT_INFO_TABLE N ON C.CLIENT_NUMBER = N.EDI_SOURCE_PRIMARY_KEY_VALUE ----072712
INNER JOIN CLIENT C2 ON M.CLIENT_NUMBER = C2.CLIENT_NUMBER

INNER JOIN PREMIUM_DETAIL P1 ON M.POLICY_NUMBER = P1.POLICY_NUMBER AND M.SEQUENCE_NUMBER = P1.SEQUENCE_NUMBER

AND P1.POLICY_DATE_TIME = (SELECT MAX (POLICY_DATE_TIME) FROM PREMIUM_DETAIL P2 WHERE P1.POLICY_NUMBER = P2.POLICY_NUMBER AND P1.WRITTEN_PREMIUM IS NOT NULL) ---10/19/12

-- WHERE ((M.EXPOSURE_TYPE = '2'AND M.TYPE_LMT IN ('4','9') AND T1.NAME = 'TYPE_ANCILLARY' AND T1.OPCO_CODE = E2.CODE ) OR (M.EXPOSURE_TYPE = '1' AND T.NAME = 'SPECIALTY' AND T.OPCO_CODE = E.CODE))
WHERE ((M.EXPOSURE_TYPE = '2'AND M.TYPE_LMT IN ('4','9') AND T1.NAME = 'TYPE_ANCILLARY' AND T1.OPCO_CODE = E2.CODE ) OR (M.EXPOSURE_TYPE = '1' AND M.TYPE_LMT <> '8' AND T.NAME = 'SPECIALTY' AND T.OPCO_CODE = E.CODE)) ---10/16/12
AND P.POL_EFF_DATE BETWEEN '1/1/12' AND '6/30/12'
AND NOT (M.POLICY_NUMBER LIKE 'MS%' OR M.POLICY_NUMBER LIKE 'Q%')
AND M.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME)FROM MPL_EXPOSURE M3 WHERE M.POLICY_NUMBER = M3.POLICY_NUMBER)
AND R.STATUS_1<>'6'
AND M.CANCEL_TYPE NOT IN ('1','2')
AND M.EXP_PREM <> 0
AND M.POLICY_TYPE IN ('2','3')----10/16/12

the question is in regards to the the last inner join

INNER JOIN PREMIUM_DETAIL P1 ON M.POLICY_NUMBER = P1.POLICY_NUMBER AND M.SEQUENCE_NUMBER = P1.SEQUENCE_NUMBER

AND P1.POLICY_DATE_TIME = (SELECT MAX (POLICY_DATE_TIME) FROM PREMIUM_DETAIL P2 WHERE P1.POLICY_NUMBER = P2.POLICY_NUMBER AND P1.WRITTEN_PREMIUM IS NOT NULL) ---10/19/12)

and one particular policy xx13423.

here is the records in the premium_detail table and mpl_exposure table for this particular policy.. but for some reason the policy is not being selected..

MPL_EXPOSURE

POLICY_DATE_TIME POLICY_NUMBER SEQUENCE_NUMBER CLIENT_NUMBER PREM EXP_TYPE CANCEL_TYPE POLICY_TYPE
2/10/2012 XX 13423 4 14140 5906 1 X 2
12/28/2011 XX 13423 4 14140 347 1 1 2
12/10/2011 XX 13423 4 14140 3474 1 X 2
3/7/2011 XX 13423 4 14140 3474 1 X 2

PREMIUM_DETAIL

POLICY_DATE-TIME POLICY_NUMBER SEQUENCE_NUMBER WRITTEN_PREMIUM
1/10/2012 XX 13423 4 432
1/10/2012 XX 13423 4 0
1/10/2012 XX 13423 4 2571
1/10/2012 XX 13423 4 432
1/10/2012 XX 13423 4 0
1/10/2012 XX 13423 4 2571
12/28/2011 XX 13423 4 432
12/28/2011 XX 13423 4 0
12/28/2011 XX 13423 4 2571
12/10/2011 XX 13423 4 500
12/10/2011 XX 13423 4 0
12/10/2011 XX 13423 4 2974
3/7/2011 XX 13423 4 500
3/7/2011 XX 13423 4 0
3/7/2011 XX 13423 4 2974

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-22 : 01:10:22

Hi divan,

Try with Left joining of MPL_Exposure.....

FROM MPL_EXPOSURE M
LEFT JOIN POLICY P ON M.POLICY_NUMBER = P.POLICY_NUMBER AND M.POLICY_DATE_TIME = P.POLICY_DATE_TIME

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources