JOIN Dropping the records - 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

JOIN Dropping the records

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2013-05-09 : 10:28:53

Hi,

I have Table A:with 419430 records, Table B with 19372 Records.

I am doing join on these 2 tables in order to calculate 2 new columns
BDGT_UTIL_AMT and BDGT_MBRSHIP_MO_CNT.

When I am joining the 2 tables records are drop, I am expecting 419430 records as result instead I am getting result of 214868 records, 204,562 records are dropped.

Is there any way I can attach the data of Table 1:dev1_metadata_etl.DLDR_BDGT_UTIL_O and Table 2:PROD_DEPT_DLDR_TBL.DLDR_BDGT_MBRSHIP_O and the data I getting as Result of 214868 records.I have them in text file.

select DLDR_RDM.FILE_CTRL_ID,DLDR_RDM.ROW_NUM, DLDR_RDM.BDGT_SCEN_CD, DLDR_RDM.HLTH_PLAN_BDGT_CD, DLDR_RDM.PDCT_BDGT_CD,
DLDR_RDM.GL_ACCT_CD, DLDR_RDM.EXPRNC_COHORT_CD, DLDR_RDM.BDGT_UTIL_MEAS_CD, DLDR_RDM.BDGT_YR_MO_NUM,
DLDR_RDM.SRC_METRIC_VALUE,CAST(((DLDR_RDM.BDGT_AMT/12000) * DLDR_MEM.BDGT_AMT) AS DECIMAL(18,8)) AS BDGT_UTIL_AMT,
DLDR_MEM.BDGT_AMT AS BDGT_MBRSHIP_MO_CNT from dev1_metadata_etl.DLDR_BDGT_UTIL_O DLDR_RDM
left outer join PROD_DEPT_DLDR_TBL.DLDR_BDGT_MBRSHIP_O DLDR_MEM
on DLDR_RDM.BDGT_SCEN_CD=DLDR_MEM.BDGT_SCEN_CD
AND DLDR_RDM.HLTH_PLAN_BDGT_CD=DLDR_MEM.HLTH_PLAN_BDGT_CD
AND DLDR_RDM.PDCT_BDGT_CD=DLDR_MEM.PDCT_BDGT_CD
AND DLDR_RDM.BDGT_YR_MO_NUM=DLDR_MEM.BDGT_YR_MO_NUM
AND DLDR_RDM.EXPRNC_COHORT_CD=DLDR_MEM.EXPRNC_COHORT_CD
WHERE DLDR_MEM.FILE_CTRL_ID = 1502

Columns of Table 1:
FILE_CTRL_ID ROW_NUM BDGT_SCEN_CD HLTH_PLAN_BDGT_CD PDCT_BDGT_CD GL_ACCT_CD EXPRNC_COHORT_CD BDGT_UTIL_MEAS_CD BDGT_YR_MO_NUM SRC_METRIC_VALUE BDGT_AMT

Table 2:
FILE_CTRL_ID ROW_NUM BDGT_SCEN_CD HLTH_PLAN_BDGT_CD PDCT_BDGT_CD GL_ACCT_CD EXPRNC_COHORT_CD BDGT_YR_MO_NUM BDGT_AMT

My result Columns:
FILE_CTRL_ID ROW_NUM BDGT_SCEN_CD HLTH_PLAN_BDGT_CD PDCT_BDGT_CD GL_ACCT_CD EXPRNC_COHORT_CD BDGT_UTIL_MEAS_CD BDGT_YR_MO_NUM SRC_METRIC_VALUE BDGT_AMT,BDGT_UTIL_AMT ,BDGT_MBRSHIP_MO_CNT

Please help

Thanks

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-05-09 : 10:35:47

Try doing a full outer join rather than a left outer join

bluestar
Posting Yak Master

133 Posts

Posted - 2013-05-09 : 10:46:47

Thanks for reply

I did full outer join and I am getting 215096 records

Left outer join: 214868

Inner join : 214868

All I know is, this is because of data but still I need to validate why they are dropping, same query is giving me expected result when run on sample of 10-30 records.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-05-09 : 11:13:20

SQL clauses are evaluated in the following order:

FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP

In your case FROM is doing the LEFT JOIN but it is then effectively being converted to an INNER JOIN by the WHERE clause.

You can either replace the WHERE with AND so the filter is part of the join or add OR DLDR_MEM.FILE_CTRL_ID IS NULL to the WHERE clause.

http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/

bluestar
Posting Yak Master

133 Posts

Posted - 2013-05-09 : 11:30:30

Thank a lot, I got it now, and with DLDR_MEM.FILE_CTRL_ID IS NULL I could validate the counts.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources