Wsrtjtyk

The DB I'm working with has three tables with identical column layouts, OPEX, NOPEX and CAPEX. I would like to query all three for items with a matching AssetId and get a single result set so that I can process them all at the same time in my .Net code.

The twist is that I do need to know which table they came from.

I know I can do this with a series of CASE in the SELECT clause, perhaps using the ID column in each where it's non-zero to decide which of the tables it came from. But I would have to have one for each column and the tables are pretty wide.

Is there some other way to solve this problem?

In order to get them into one set, you would use a combination of UNION and EXISTS() checks. The UNION ALL will give you a single result set that contains data from all three tables, and the EXISTS check on each will confirm the table you are querying from has corresponding records in the other tables.

SELECT *, 'OPEX' AS table_name 

FROM OPEX o

WHERE EXISTS (

SELECT 1 

FROM NOPEX n

WHERE n.asset_id = o.asset_id)

AND EXISTS (

SELECT 1 

FROM CAPEX c

WHERE c.asset_id = o.asset_id)



UNION ALL 



SELECT *, 'NOPEX' AS table_name 

FROM NOPEX n

WHERE EXISTS (

SELECT 1 

FROM Opex o

WHERE o.asset_id = n.asset_id)

AND EXISTS (

SELECT 1 

FROM CAPEX c

WHERE c.asset_id = n.asset_id)



UNION ALL 



SELECT *, 'CAPEX' AS table_name  

FROM CAPEX c

WHERE EXISTS (

SELECT 1 

FROM Opex o

WHERE o.asset_id = c.asset_id)

AND EXISTS (

SELECT 1 

FROM NOPEX n

WHERE n.asset_id = c.asset_id)

I guess you could also do INNER JOINs?

SELECT c.*, 'CAPEX' AS table_name  

FROM CAPEX c

INNER JOIN OPEX o

ON o.asset_id = c.asset_id

INNER JOIN NOPEX n

ON n.asset_id = c.asset_id



UNION ALL 



SELECT o.*, 'OPEX' AS table_name 

FROM OPEX o

INNER JOIN CAPEX c

ON c.asset_id = o.asset_id

INNER JOIN NOPEX n

ON n.asset_id = o.asset_id



UNION ALL 



SELECT n.*, 'NOPEX' AS table_name 

FROM NOPEX n

INNER JOIN OPEX o

ON o.asset_id = n.asset_id

INNER JOIN CAPEX c

ON c.asset_id = n.asset_id

Similar answer to dfundako, but resolving sooner where AssetId is in all three tables and less hitting of the indexes on the related tables:

;with cte as (

    select

    AssetID

    from (

        select distinct

        AssetID

        from Opex

        union all

        select distinct

        AssetID

        from Nopex

        union all

        select distinct

        AssetID

        from Capex

        ) as AssetIDs

    group by AssetId

    having count(AssetId) = 3

    )



select 'Opex', * from Opex as o

inner join cte 

    on o.AssetID = cte.AssetID

union all

select 'Nopex', * from Nopex as n

inner join cte 

    on n.AssetID = cte.AssetID

union all

select 'Capex', * from Capex as c

inner join cte 

    on c.AssetID = cte.AssetID