Outer Join help - output not what was expected

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

SQL Server 2000 Forums

Transact-SQL (2000)

Outer Join help - output not what was expected

Author

Topic

jej1216
Starting Member

27 Posts

Posted - 2008-02-04 : 11:19:45

SQL Server 2000

Issue:
An Asset might have any/some/all of three BOOKS ('FED', 'CORP','AMT').
I'm trying to find those Assets without an AMT BOOK.

I have this SQL:

select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
from PS_BOOK A
right outer join PS_BOOK B
on A.BUSINESS_UNIT = B.BUSINESS_UNIT and
A.ASSET_ID = B.ASSET_ID
where B.BOOK <> 'AMT'
order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK

the output is not what I need - it lists Assets multiple times, even
those with 'AMT' books. Example output:
BUSINESS_UNIT ASSET_ID BOOK
------------- ------------ ----------
00010 000000000001 AMT
00010 000000000001 AMT
00010 000000000001 CORP
00010 000000000001 CORP
00010 000000000001 FEDERAL
00010 000000000001 FEDERAL

Should I not be using an outer join?

TIA, jej1216

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 11:40:31

There is no problem with query results. You are selecting A.BOOK and filtering on B.BOOK<> 'AMT'. thats why result has records with BOOK value AMT appearing.

To answer on whether you shouldnt be using OUTER JOIN i need to know more on your requirement, table structures, sample data and o/p you want. If you can give this info, then i will be able to guide you on this.

jej1216
Starting Member

27 Posts

Posted - 2008-02-04 : 12:25:34

visakh16,

Thanks for your assistance.

Here is the table structure:
BUSINESS_UNIT (PK), ASSET_ID (PK), BOOK (PK), IN_SERVICE_DT, IN_SERVICE_PD, IN_SERVICE_FY,
DEPR_IN_SERVICE_SW, BEGIN_DEPR_DT, BEGIN_DEPR_PD, BEGIN_DEPR_FY, BEGIN_FRACTION, DEPR_CALC_SW,
BEGIN_CALC_DT, BEGIN_CALC_PD, BEGIN_CALC_FY, CALCULATION_TYPE, LAST_CALC_TYPE, RSV_FRACTION,
RECALC_RSV_SW, SCHED_FRACTION, YEAR_REM_FRACTION, DEPR_STATUS, REGULATION, RECOVERY_LIFE,
RECOVERY_SUBTYPE, FUTURE_DEPR_YEARS, CONVENTION, LIFE, LIFE_REMAINING, TAX_CLASS_AM,
DEPR_PERCENT, DB_PERCENT, METHOD, DEPR_SCHED_CD, DEPR_LIMIT_CD, PROPERTY_CD, PROPERTY_TYPE,
SALVAGE_VAL, SALVAGE_PCT, TAX_CREDIT_SW, BASIS_RED_TOTAL, BASIS_RED_RECAP, SEC179_TAKEN_SW,
SEC179_AMT, LIFE_END_DT, DEPR_LOW_LIMIT, SUSPEND_STATUS, SUSPEND_DT, RESUME_DT, SUSPENDED_PERIODS,
COST_BASIS_LIMIT, DEPR_AVG_OPTION, GROUP_ASSET_ID, UOP_ID, CURRENCY_CD, INCLUDE_CAP_GAINS,
UD_METHOD_ID, PERIOD_DEPR_SW, ACCOUNTING_MTHD, DEPR_PASS_LIFE_SW, LISTED_PROP, LISTED_PROP_TYPE,
BUS_USE_PERCENT, CHILD_TK_PARENT_SW, ARCHIVE_ID, SEC_291, DEPR_LIMIT_PCT, MULTI_SHIFT_CODE,
MULTI_SHIFT_NBR, SPECIAL_TERMS, ACCELERATED_TERMS, INITIAL_TERMS, INCREASE_RATE,
SPECIAL_DEPR_SW, LIFE_IN_YRS, RETIREMENT_DT, YEAR_CHANGE, LOW_VALUE_SW, BUSINESS_TAX_FLG, BUSINESS_TAX_BASIS, LAST_BK_DTTM_STAMP, LAST_TRANS_DT, LAST_ACCOUNTING_DT, IUT_DEPR_STATUS,
SUS_DEPR_ACCUM, SUS_DEPR_YTD, SUS_DTTM_STAMP, AM_BK_UD_CHAR1, AM_BK_UD_CHAR2, AM_BK_UD_DATE1,
AM_BK_UD_NUM1, AM_BK_UD_NUM2, DEROGATORY_SW, CCA_50_FLG, CF_SEQNO_LAST, END_DEPR_DT,
END_DEPR_FY, END_DEPR_PD, RETIRED_SW, RETIRE_CONVENTION, DEPR_BONUS_SW, DEPR_BONUS_PCT,
TOT_OBLIGATION, IMPAIR_FLG, LAST_IMPAIR_DT

A BUSINESS_UNIT / ASSET_ID should have 3 BOOK rows ('CORP','FED','AMT') and a BUSINESS_UNIT / ASSET_ID can also have a 4th BOOK ('LOCAL').
Data entry errors resulted in numerous BUSINESS_UNIT / ASSET_ID's not having the 'AMT' BOOK row.

I need to find those BUSINESS_UNIT / ASSET_ID's.

TIA,

jej1216

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 12:34:32

I think if you just wants to find out BUSINESS_UNIT / ASSET_ID's without AMT book rows,this is enough

SELECT a.BUSINESS_UNIT,a.ASSET_ID
FROM PS_BOOK a
LEFT OUTER JOIN (SELECT BUSINESS_UNIT,ASSET_ID
FROM PS_BOOK
WHERE BOOK='AMT') b
ON b.BUSINESS_UNIT=a.BUSINESS_UNIT
AND b.ASSET_ID=a.ASSET_ID
WHERE b.BUSINESS_UNIT IS NULL
AND b.ASSET_ID IS NULL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-05 : 07:52:51

quote:
Originally posted by visakh16

I think if you just wants to find out BUSINESS_UNIT / ASSET_ID's without AMT book rows,this is enough

SELECT a.BUSINESS_UNIT,a.ASSET_ID
FROM PS_BOOK a
LEFT OUTER JOIN (SELECT BUSINESS_UNIT,ASSET_ID
FROM PS_BOOK
WHERE BOOK='AMT') b
ON b.BUSINESS_UNIT=a.BUSINESS_UNIT
AND b.ASSET_ID=a.ASSET_ID
WHERE b.BUSINESS_UNIT IS NULL
AND b.ASSET_ID IS NULL

Visakh, are you too humble to reply this?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96725

Madhivanan

Failing to plan is Planning to fail

jej1216
Starting Member

27 Posts

Posted - 2008-02-05 : 11:21:27

Thanks,

That's exactly what I needed.

jej1216

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 11:32:26

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

I think if you just wants to find out BUSINESS_UNIT / ASSET_ID's without AMT book rows,this is enough

SELECT a.BUSINESS_UNIT,a.ASSET_ID
FROM PS_BOOK a
LEFT OUTER JOIN (SELECT BUSINESS_UNIT,ASSET_ID
FROM PS_BOOK
WHERE BOOK='AMT') b
ON b.BUSINESS_UNIT=a.BUSINESS_UNIT
AND b.ASSET_ID=a.ASSET_ID
WHERE b.BUSINESS_UNIT IS NULL
AND b.ASSET_ID IS NULL

Visakh, are you too humble to reply this?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96725

Madhivanan

Failing to plan is Planning to fail

Thanks madhi for the wishes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources