CODEWITHSUNDEEP
sql
murso
murso

Reputation: 3

SQL Join multiple tables to one view

What i'm trying to do is get data from several tables (+50) and make this available in a general view.

The "ObjNr" column can exist several times in each table, so I want to get the record with the latest timestamp.

See this link for a better explanation of my problem including the source tables and the desired result:

Database overview

I have tried several scenarios like unpivoting,..., using c# to do this in the background, ... but i'm really curious what the best way would be to accomplish this.

Thanks in advance!

Upvotes: 0

Views: 56

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Rank your rows with ROW_NUMBER. Then apply UNION ALL.

select
  id, objnr, objname, datetime, batchnr, batchnr_component_name
from
(
  select
    id, objnr, objname, datetime, batchnr, batchnr_component_name,
    row_number() over (partition by objnr order by datetime desc) as rn
  from table1
  union all
  select
    id, objnr, objname, datetime, batchnr, batchnr_component_name,
    row_number() over (partition by objnr order by datetime desc) as rn
  from table2
  union all
  select
    id, objnr, objname, datetime, batchnr, batchnr_component_name,
    row_number() over (partition by objnr order by datetime desc) as rn
  from table3
  union all
  select
    id, objnr, objname, datetime, batchnr_2, batchnr_2_component_name,
    row_number() over (partition by objnr order by datetime desc) as rn
  from table3
) ranked
where rn = 1;

Upvotes: 1

Related Questions