Question regarding use of a join to the same table

I have a table, BOOK, which uses BU and Asset and BOOK as a combined unique
identifier. A BU/Asset combination can have up to 4 rows, with different
BOOK values (CORP, FED, AMT - all of which are required, and LOCAL which is
situational).
Due to bad data entry, some of the Assets were not given an AMT row. I have
to identify those BU/Asset combinations.
I know I need to do a join back to the BOOK table, but I can't seem to get
the right statement. Here is my 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
This gives me a double lisitng of rows (of course) and I can visually scan
for those missing 'AMT', but that's way too clunky. Where am I shooting
myself in the foot? What is the correct statement to use to get just a list
of the BU/Asset combinations that are missing 'AMT'?
TIA,
jej1216I tried a simpler stetment:
select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
from PS_BOOK A
where EXISTS (select 'x'
from PS_BOOK B
where B.BOOK <> 'AMT')
order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
But I still get both the BU/Asset combination that has 'AMT' as well as the
combination that does not have 'AMT'.
- jej1216
"Joe" wrote:
> I have a table, BOOK, which uses BU and Asset and BOOK as a combined unique
> identifier. A BU/Asset combination can have up to 4 rows, with different
> BOOK values (CORP, FED, AMT - all of which are required, and LOCAL which is
> situational).
> Due to bad data entry, some of the Assets were not given an AMT row. I have
> to identify those BU/Asset combinations.
> I know I need to do a join back to the BOOK table, but I can't seem to get
> the right statement. Here is my 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
> This gives me a double lisitng of rows (of course) and I can visually scan
> for those missing 'AMT', but that's way too clunky. Where am I shooting
> myself in the foot? What is the correct statement to use to get just a list
> of the BU/Asset combinations that are missing 'AMT'?
> TIA,
> jej1216
>|||On Tue, 16 Oct 2007 08:42:02 -0700, Joe wrote:
>I tried a simpler stetment:
>select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
>from PS_BOOK A
>where EXISTS (select 'x'
> from PS_BOOK B
> where B.BOOK <> 'AMT')
>order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
>But I still get both the BU/Asset combination that has 'AMT' as well as the
>combination that does not have 'AMT'.
Hi Joe,
I think you want this one:
SELECT a.BusinessUnit, a.AssetID, a.Book
FROM PS_Book AS a
WHERE NOT EXISTS
(SELECT *
FROM PS_Book AS b
WHERE b.BusienssUnit = a.BusinessUnit
AND b.AssetID = a.AssetID
AND b.Book <> 'AMT');
If that is not it, then see www.aspfaq.com/5006 to find the information
you need to post in order to make it possible for us to help you.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis