riccy riccy - 1 month ago 6
MySQL Question

SQL Join two tables, only get latest entry of second table

I have these two tables:

items

itemname description belongs
A1 some_text user1
A2 some_text user1
A3 some_text user1
A4 some_text user1
A5 some_text user1
A1 some_text user2
B2 some_text user2


movements

itemname start_date end_date belongs
A1 2013-02-01 2014-01-12 user1
A1 2014-08-14 NULL user1
A1 2014-10-15 2015-01-01 user1
A2 2013-08-03 2014-08-14 user1
A2 2014-08-14 NULL user1
A3 2013-08-02 2014-08-20 user1
A3 2013-12-05 2014-01-07 user1
A4 2013-07-15 2014-09-13 user1
A4 2014-09-13 NULL user1
A5 2013-07-15 2014-09-13 user1
A5 2015-03-11 2016-03-12 user1
A5 2016-03-12 2016-04-13 user1
A1 2015-08-01 2015-08-12 user2
B2 2015-08-13 2015-08-23 user2


I was playing around with joins and max(date) but didn't come to a working solution.

A result for user1 should looks like this:

itemname description belongs start_date end_date
A1 some_text user1 2014-08-14 NULL
A2 some_text user1 2014-08-14 NULL
A3 some_text user1 2013-08-02 2014-08-20
A4 some_text user1 2014-09-13 NULL
A5 some_text user1 2016-03-12 2016-04-13


I need the line (movement) with the highest (latest, newest) end_date if there is no line where:

end_date = NULL


If there is a line where end_date = NULL, I need this line for that item.

Difficulty here is that sorting for max(start_date) would not work since sometimes there is a timeperiod inside another timeperiod for one item.

I hope you could understand my problem.

Greetings from germany :)

Answer

You need something like this

Find the most recent start date for each itemname & belongs combination then join the result back with max start date to get the result

SELECT i.itemname,i.description,i.belongs,m.start_date,m.end_date
FROM   items i
       JOIN movements m
         ON i.itemname = m.itemname
            AND i.belongs = m.belongs
       JOIN (SELECT itemname,
                    belongs,
                    Max(COALESCE(end_date, start_date)) AS max_dt,
                    Max(end_date)                       AS max_end_dat,
                    Max(start_date)                       AS max_start_dat
             FROM   movements
             GROUP  BY itemname,
                       belongs) m1
         ON m1.itemname = m.itemname
            AND m1.belongs = m.belongs
            AND ( ( m.end_date = m1.max_dt
                    AND m1.max_dt = m1.max_start_dat )
                   OR ( m1.max_dt = COALESCE(end_date, m.start_date)
                        AND m1.max_start_dat <> m1.max_dt )
                   OR ( m1.max_dt = m.start_date
                        AND m1.max_end_dat <> m1.max_dt ) )
ORDER  BY i.belongs,
          i.itemname 

Update :

SELECT i.itemname,
       i.description,
       i.belongs,
       m.start_date,
       m.end_date
FROM   items i
       JOIN movements m
         ON i.itemname = m.itemname
            AND i.belongs = m.belongs
       JOIN (SELECT itemname,
                    belongs,
                    Max(start_date) AS max_dat,
                    'st'            AS indi
             FROM   movements
             WHERE  end_date IS NULL
             GROUP  BY itemname,
                       belongs
             UNION ALL
             SELECT itemname,
                    belongs,
                    Max(end_date) AS max_dat,
                    'ed'
             FROM   movements m
             WHERE  NOT EXISTS (SELECT 1
                                FROM   movements m1
                                WHERE  m.itemname = m1.itemname
                                       AND m.belongs = m1.belongs
                                       AND end_date IS NULL)
             GROUP  BY itemname,
                       belongs) m1
         ON m1.itemname = m.itemname
            AND m1.belongs = m.belongs
            AND ( ( m1.max_dat = m.start_date
                    AND indi = 'st' )
                   OR ( m1.max_dat = m.end_date
                        AND indi <> 'st' ) )
ORDER  BY i.belongs,
          i.itemname 

This will be really easy if your RDBMS supports ROW_NUMBER window function or APPLY operator

Comments