This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
Hello...
Can anyone help me with an OUTER JOIN issue... My SQL statement below is working except that as I add each table my result set is being limited... I know it is because I need to use OUTERJOINS but don't konw how to do so w/more than one table... I need the data from all 3 tables to show up anytime there is a value in any of the 3 tables... Can anyone help me? Thanks in advance!
SELECT asset.assetnum AS 'ASSETID', asset.assettag AS 'MAKE / MODEL', asset.barcode AS 'BARCODE', asset.bay AS 'BAY', asset.tile AS 'TILE', asset.rfa_devcdu1 AS 'CDU 1', asset_1.assetnum AS 'BREAKER 1', asset_1.cb_rppassetdesc AS 'RPP 1', asset.rfa_devcdu2 AS 'CDU 2', asset_2.assetnum AS 'BREAKER 2', asset_2.cb_rppassetdesc AS 'RPP 2'
FROM AMEXMAXIMO.dbo.asset asset, AMEXMAXIMO.dbo.asset asset_1, AMEXMAXIMO.dbo.asset asset_2
WHERE asset_1.cb_cduassetnum = asset.rfa_devcdu1 AND asset_2.cb_cduassetnum = asset.rfa_devcdu2 AND ((asset.assetnum Like '%RFDEV%') AND (asset.assettype='cdu fed') AND (asset.status='INST ASST MGT'))
Hi, If I understand what you are trying to do, try this.
SELECT asset.assetnum AS 'ASSETID', asset.assettag AS 'MAKE / MODEL',
asset.barcode AS 'BARCODE', asset.bay AS 'BAY', asset.tile AS 'TILE',
asset.rfa_devcdu1 AS 'CDU 1', asset_1.assetnum AS 'BREAKER 1',
asset_1.cb_rppassetdesc AS 'RPP 1', asset.rfa_devcdu2 AS 'CDU 2',
asset_2.assetnum AS 'BREAKER 2', asset_2.cb_rppassetdesc AS 'RPP 2'
FROM AMEXMAXIMO.dbo.asset asset
left outer join AMEXMAXIMO.dbo.asset asset_1
on asset_1.cb_cduassetnum = asset.rfa_devcdu1
left outer join AMEXMAXIMO.dbo.asset asset_2
on asset_2.cb_cduassetnum = asset.rfa_devcdu2
WHERE ((asset.assetnum Like '%RFDEV%') AND (asset.assettype='cdu
fed') AND (asset.status='INST ASST MGT'))
This is the syntax for SQL SERVER
--- In MAXIMO@yahoogroups.com, "hankinskeith@..." <keith.d.hankins@...> wrote:
>
> Hello...
> Can anyone help me with an OUTER JOIN issue... My SQL statement below is working except that as I add each table my result set is being limited... I know it is because I need to use OUTERJOINS but don't konw how to do so w/more than one table... I need the data from all 3 tables to show up anytime there is a value in any of the 3 tables... Can anyone help me? Thanks in advance!
>
> SELECT asset.assetnum AS 'ASSETID', asset.assettag AS 'MAKE / MODEL', asset.barcode AS 'BARCODE', asset.bay AS 'BAY', asset.tile AS 'TILE', asset.rfa_devcdu1 AS 'CDU 1', asset_1.assetnum AS 'BREAKER 1', asset_1.cb_rppassetdesc AS 'RPP 1', asset.rfa_devcdu2 AS 'CDU 2', asset_2.assetnum AS 'BREAKER 2', asset_2.cb_rppassetdesc AS 'RPP 2'
> FROM AMEXMAXIMO.dbo.asset asset, AMEXMAXIMO.dbo.asset asset_1, AMEXMAXIMO.dbo.asset asset_2
> WHERE asset_1.cb_cduassetnum = asset.rfa_devcdu1 AND asset_2.cb_cduassetnum = asset.rfa_devcdu2 AND ((asset.assetnum Like '%RFDEV%') AND (asset.assettype='cdu fed') AND (asset.status='INST ASST MGT'))
>