CODEWITHSUNDEEP
mysqlsql
jerome
jerome

Reputation: 91

select two tables with different condtion

enter image description here

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?

Upvotes: 1

Views: 876

Answers (1)

Utsav
Utsav

Reputation: 8093

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

Upvotes: 1

Related Questions