select two tables with different condtion

Question

The table1 has only index column and the table2 is just to contain versions.

I want to select every index and its latest version. (the yellow cells)

The table2 has datetime column to know which one is latest.

When table1.idx = table2.belongTo, how to combine tables with a single query?

Possible duplicate of [SQL - Combine two tables for one output](http://stackoverflow.com/questions/1227835/sql-combine-two-tables-for-one-output) — davejal, Dec 29 '15 at 03:06
Could you try to add your exact db structure? An sqlfiddle would be great. — davejal, Dec 29 '15 at 03:08

score 1 · Accepted Answer · edited May 23 '17 at 12:15

Edit; Corrected the answer to return all columns from table2.

You first have to derive the second table by selecting max(datetime) and then join based on the condition you gave.

Here the second table t2 will create row_number based on max(datetime) value for each BelongTo. Thanks to this answer for it.

select t1.*,t2.idx,t2.belongTo,t2.datetime1
from table1 t1 
inner join 
(
    select t11.idx,t11.belongTo,t11.datetime1,count(*) as row_number from 
    table2 t11
    inner join table2 t12
    on t11.belongTo=t12.belongTo
    and t11.datetime1 <= t12.datetime1
    group by t11.belongTo,t11.datetime1
) t2 /*this table will create row_number based on max datetime value for each belongTo*/
on t1.idx=t2.belongTo
where t2.row_number=1

See SQL Fiddle demo here

http://sqlfiddle.com/#!9/e5ada/10

select two tables with different condtion

1 Answers1