How to JOIN two tables on behalf of Max date

Question

I have tried following. Query is working but returning more rows as not expected.

  SELECT x,y,z FROM 
    (SELECT (t1.HOUR*4) + INT (t1.MINUTS/15 ) +1 AS x,t1.AMOUNT as y, t1.DATE,t2.AMOUNT AS z FROM table1 t1 
    INNER JOIN 
    (SELECT * FROM table2 WHERE DATE=(SELECT MAX(DATE) FROM table2 ))AS t2
    ON t1.STORE=t2.STORE
    WHERE t1.DATE = (SELECT MAX(DATE) FROM table1 ))

Sample data:=

STORE   DATE    DAY_OF_WK   HOUR    MINUTS  AMOUNT
574     13-03-2017  2         0      0       0.00
574     13-03-2017  2         0      15      0.00
574     12-03-2017  2         0      30      0.00
574     11-03-2017  2         0      45      0.00
574     11-03-2017  2         1      0       0.00
574     13-03-2017  2         1      15      0.00

Both table having same data but there is different business logic on amount column.

What I need is to get amount and time id from both tables only of max date.

Please explain or show more detail when you state something like "more rows than expected". Precisely what do you expect? You state it in words, but it is often more useful to show an example of what you expect. — C Perkins, Sep 12 '17 at 02:44
Maybe a TOP N nested query will help. Review http://allenbrowne.com/subquery-01.html#TopN — June7, Sep 12 '17 at 02:52
Your problem is likely that you are only joining the tables on one non-unique value: STORE. The default behavior of any join is to produce the Cartesian product of rows from each table. In other words, if you don't define unique conditions, the query will return all possible combinations from all tables that satisfy the conditions. For example, if there are 3 rows from each table with max date (13-03-2017 as in your sample data), the query will match STORE 574 but will return 3 x 3 = 9 rows. You need to decide what other condition is necessary to return the limited set of results you expect. — C Perkins, Sep 12 '17 at 02:53
Possible duplicate of [Select from two tables giving more rows than expected](https://stackoverflow.com/questions/8840010/select-from-two-tables-giving-more-rows-than-expected) The Max date conditions look correct / sufficient. — C Perkins, Sep 12 '17 at 03:01

score 0 · Accepted Answer · answered Sep 12 '17 at 11:27

Solved with following query=>

SELECT T1.id as x,y,z from 
(SELECT (HC.HOUR * 4) + INT (HC.MINUTS/15 ) + 1 AS id, AMOUNT AS y, DATE FROM table1 HC WHERE DATE = (SELECT MAX(DATE) FROM table1))T1
LEFT JOIN
(SELECT (HS.HOUR * 4) + INT (HS.MINUTS/15 ) + 1 AS id, AMOUNT as z, DATE FROM table2 HS WHERE DATE=(SELECT MAX(DATE) FROM table2 ))t2 ON T1.id=T2.id

How to JOIN two tables on behalf of Max date

1 Answers1