I have three tables with id's like

table 1:

table 2:

table 3:

I want to join the tables on the maximum id that each tables share, in this case the maximum id that each table has is 13. How can I do this in SQL?

CodePudding user response：

This is an example, obviously you can replace with your correct information:

select TableOne.id
from TableOne
inner join (select max(id) as 'Id' from TableTwo) as TableTwo ON TableOne.Id = TableTwo.Id
inner join (select max(id) as 'Id' from TableThree) as TableThree ON TableTwo.Id = TableThree.Id
inner join (select max(id) as 'Id' from TableFour) as TableFour ON TableThree.Id = TableFour.Id

CodePudding user response：

Simple join followed by selecting max should do the trick:

-- sample data
WITH dataset(id) AS (
    VALUES (1),
    (2),
    (3),
    (5)
),
dataset1(id) AS (
    VALUES (1),
    (3)
),
dataset2(id) AS (
    VALUES (1),
    (2),
    (3)
) 

-- query
SELECT max(d.id)
from dataset d
join dataset1 d1 on d.id = d1.id
join dataset1 d2 on d.id = d2.id

Output: