MySQL Get latest record by date from mutiple tables with values from lookup tables

Question

I want to get the latest MainNumber, Serial, BarType and Notes for a given MainNumber, if they exist. Note that BarType is stored in a lookup table and referenced with BarID.

Unreason came up with this:

SELECT @MainNumber, COALESCE(n.Notes, 'None')
FROM numbers 
     LEFT JOIN notes n ON numbers.MainNumber = n.MainNumber
     LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date < n2.Date
WHERE n2.Date IS NULL AND numbers.MainNumber = @MainNumber

This is fine whether Notes is NULL or not, but now I need the Serial and the BarType. A MainNumber may have been assigned to multiple Serials during its lifetime, but I only want the latest Serial. (I'll need to do this with about 15 other fields in other tables, so a performant answer would be appreciated where possible)

Tables

Numbers Table:

CREATE TABLE `numbers` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `MainNumber` varchar(11) NOT NULL,
  `Serial` varchar(20) NOT NULL,
  `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `Serial` (`Serial`)
) ENGINE=MyISAM AUTO_INCREMENT=460 DEFAULT CHARSET=latin1

Notes table:

CREATE TABLE `notes` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `MainNumber` varchar(11) NOT NULL,
  `Notes` longtext NOT NULL,
  `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  KEY `MainNumber` (`MainNumber`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

ref_bars table:

CREATE TABLE `ref_bars` (
  `BarID` varchar(6) NOT NULL,
  `BarType` varchar(30) NOT NULL,
  PRIMARY KEY  USING BTREE (`BarID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

bars table:

CREATE TABLE `bars` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `MainNumber` varchar(11) NOT NULL,
  `BarID` varchar(6) NOT NULL,
  `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  KEY `MainNumber` (`MainNumber`)
) ENGINE=MyISAM AUTO_INCREMENT=212 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

Sample Data

SELECT * FROM notes:

'ID','MainNumber','Notes','Date'
'1','1','repaired','2009-03-23 12:00:00'
'2','1','replaced','2009-08-15 19:20:05'

Note: two rows for MainNumber = 1, but no row for a MainNumber of 2. The IDs are just technical and are never used.

SELECT * FROM numbers:

'ID','MainNumber','Serial','Date'
'1','1','4642785154854','2008-08-15 12:30:00'
'2','1','4642315642316','2009-08-15 12:50:00'
'3','2','5412558456223','2010-08-15 11:30:00'

SELECT * FROM bars:

'ID','MainNumber','BarID','Date'
'1','1',1,'2008-08-15 12:30:00'
'2','1',2,'2009-08-15 12:50:00'
'3','2',2,'2010-08-15 11:30:00'

SELECT * FROM ref_bars:

'BarID','BarType'
'1','Banned'
'2','Payment required'

Expected Output

MainNumber = 1

MainNumber,Notes,Banned,Unpaid
'1','replaced','Yes','Yes'

MainNumber = 2

MainNumber,Notes,Banned,Unpaid
'2','None','No','Yes'

Edit: Fixed it and tested it, whilst making things clearer (hopefully). I was rushed off to do other things earlier today, sorry for wasting people's time with a badly-written, incomplete question.

Updated to clarify the more complex requirements

Answer 1

Maybe something like this?

LEFT JOIN (select notes n
JOIN (
    SELECT
        MAX(Date) AS Date,
        MainNumber
    FROM notes
    GROUP BY MainNumber
) AS nx
    ON n.MainNumber = nx.MainNumber AND n.Date = nx.Date) n
ON notes.MainNumber = main.MainNumber

I removed the where-clause since you don't describe why you have it. If you need it you can just insert it again.

EDIT: The query is updated, but it is still hard to understand exatcly what you want. When I wrote example data I meant 2-3 rows for each table together with expected output, and if you could simplify the example that would be even better. Hope it helps.

Answer 2

Choosing to ignore your initial queries since they do not reproduce your problem I am looking at you expected output I assume that you are trying to achieve the following:

Given MainNumber look for the record in tabel notes and return the row with max date, if there are no records in table notes for a given MainNumber then return constant 'None'

(this might not be what is requested, so please correct the expected output if it is not)

This can be easily achieved with, for example

SELECT @MainNumber, n.Notes
FROM notes n
     LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date < n2.Date
WHERE n2.Date IS NULL AND n.MainNumber = @MainNumber

Which will return the latest row from notes. Now from the application side if it does not return any rows just print @MainNumber, 'None' and that is it...

If you look for pure SQL (and assuming that you do need some other columns from the numbers table) then you can do:

SELECT @MainNumber, COALESCE(n.Notes, 'None')
FROM numbers 
     LEFT JOIN notes n ON numbers.MainNumber = n.MainNumber
     LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date < n2.Date
WHERE n2.Date IS NULL AND numbers.MainNumber = @MainNumber

EDIT: The first query is tested

mysql> SET @MainNumber = 1;                                                     Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @MainNumber, n.Notes FROM notes n      LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date < n2.Date WHERE n2.Date IS NULL AND n.MainNumber = @MainNumber;
+-------------+----------+
| @MainNumber | Notes    |
+-------------+----------+
|           1 | replaced | 
+-------------+----------+
1 row in set (0.00 sec)

The second query initially returned multiple rows in case of multiple entries in the numbers table, DISTINCT fixes that

mysql> SET @MainNumber = 1;                                                     Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT @MainNumber, COALESCE(n.Notes, 'None')
    -> FROM numbers 
    ->      LEFT JOIN notes n ON numbers.MainNumber = n.MainNumber
    ->      LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date < n2.Date
    -> WHERE n2.Date IS NULL AND numbers.MainNumber = @MainNumber
    -> ;
+-------------+---------------------------+
| @MainNumber | COALESCE(n.Notes, 'None') |
+-------------+---------------------------+
|           1 | replaced                  | 
+-------------+---------------------------+
1 row in set (0.00 sec)

mysql> SET @MainNumber = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT @MainNumber, COALESCE(n.Notes, 'None')
    -> FROM numbers 
    ->      LEFT JOIN notes n ON numbers.MainNumber = n.MainNumber
    ->      LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date < n2.Date
    -> WHERE n2.Date IS NULL AND numbers.MainNumber = @MainNumber
    -> ;
+-------------+---------------------------+
| @MainNumber | COALESCE(n.Notes, 'None') |
+-------------+---------------------------+
|           2 | None                      | 
+-------------+---------------------------+
1 row in set (0.00 sec)

Answer 3

You can do it with a JOIN, as suggested by Unreason.

Another way would be with a subquery:

select distinct s.MainNumber, 
COALESCE(
  (select n.notes from notes n where n.MainNumber=s.MainNumber order by n.Date desc limit 1), 
  'None') as LastNote
from numbers s
WHERE s.MainNumber=?

Note that the solution using JOIN may or may not perform better, you'll have to try it.

Also note that "LIMIT" is MySQL-specific (not ANSI SQL), so take care if you intend to migrate to another DBMS.

Answer 4

SELECT MainNumber,
       COALESCE(Notes,"None") as Notes,
       if(BarID=1,"Yes","No") as Banned,
       if(BarID=2,"Yes","No") as Unpaid,
       COALESCE(notes.Date,CURRENT_TIMESTAMP) as Date
FROM numbers LEFT JOIN notes USING (MainNumber)
     LEFT JOIN bars USING (MainNumber)
GROUP BY MainNumber,Date
HAVING Date=COALESCE(MAX(notes.Date),CURRENT_TIMESTAMP)

Answer 5

I finally worked it out. It's actually a lot more simple than I thought. It's closer to the original query I was writing, too.

SELECT
    s.MainNumber,
    n.Notes
FROM numbers s
JOIN (
    SELECT
        MAX(Date) AS Date,
        MainNumber
    FROM numbers
    WHERE MainNumber = 2
) AS sx
    ON s.MainNumber = sx.MainNumber AND s.Date = sx.Date

LEFT JOIN notes n
    ON s.MainNumber = n.MainNumber
LEFT JOIN (
    SELECT
            MAX(Date) AS Date,
            COALESCE(MainNumber,0) AS MainNumber
    FROM notes
    WHERE MainNumber = 2
) AS nx
    ON n.MainNumber = nx.MainNumber AND n.Date = nx.Date