MySQL query with join on derived table containing group by, return most recent related record

Hi everyone

I've got a MySQL query which uses joins on a couple of derived tables. One of the derived queries works great, and I'm getting the correct number of rows returned.

But I'm also trying to display some additional info from a related table for each row and this is where I'm struggling. I've read several other questions on here with similar questions but I can't manage to apply it to my query.

Here's the query:

SELECT `adbookings`.`company_id`, `companies`.`company_name`, `adbookings`.`run`, `adbookings`.`bill_freq`, `adbookings`.`currency`, FORMAT(`adinserts`.`rate`, 2) AS `rate`, `publications`.`title`, CONCAT(FLOOR(CAST(UNIX_TIMESTAMP()-`invoices`.`last_invoice_stamp` AS SIGNED INTEGER)/86400), ' days ago') AS `last_invoice_days_ago`, `invoices`.`last_invoice_no`, `invoices`.`last_invoice_reference`
FROM `adinserts` 
INNER JOIN 
(
SELECT `publications`.`publication_id`, `publications`.`art_stamp`, `publications`.`title`
FROM `publications`
LEFT JOIN `adinserts` ON `publications`.`publication_id`=`adinserts`.`publication_id` AND `adinserts`.`invoice_id` IS NOT NULL
WHERE `publications`.`publication_stamp`>=UNIX_TIMESTAMP('2010-01-01 00:00:00')
GROUP BY `publications`.`publication_id`
HAVING COUNT(`adinserts`.`invoice_id`)>0
) AS `publications` ON `adinserts`.`publication_id`=`publications`.`publication_id`
LEFT JOIN `adbookings` ON `adinserts`.`booking_id`=`adbookings`.`booking_id` 
LEFT JOIN
(
SELECT `company_id`, CONCAT_WS('', `prefix`, `invoice_id`, `suffix`) AS `last_invoice_no`, MAX(`invoices`.`invoice_stamp`) AS `last_invoice_stamp`, `reference` AS `last_invoice_reference` 
FROM `invoices`
GROUP BY `invoices`.`company_id`
) AS `invoices` ON `adbookings`.`company_id`=`invoices`.`company_id`
LEFT JOIN `companies` ON `adbookings`.`company_id`=`companies`.`company_id` 
WHERE `adinserts`.`invoice_id` IS NULL AND `adinserts`.`cancel_stamp` IS NULL AND `adinserts`.`rate`>0
ORDER BY `publications`.`art_stamp`, `companies`.`company_name`

My problem is with the second derived table... LEFT JOIN (...) AS invoices

I'm trying to get the invoice_stamp of the most recent related invoice for that company, using MAX(invoice_stamp). That appears to work and does return the related record with the开发者_JAVA技巧 largest value of invoice_stamp (a Unix timestamp).

But I'm also trying to get the matching invoice_id and reference fields of that record returned by the MAX() aggregate function. But the invoice_id and reference fields do not match the record returned by the MAX() function.

Anyone know how I can get the full details of the record returned by the aggregate function in the derived table?

Based on an AdInsert being a 1:1 ratio to Publications, and those being a 1:1 to the bookings and ultimately the company, we don't need to backlink to AdInserts on the same ID looking for an invoice we'll just be ignoring.

THESE should be the qualified ad inserts you are looking for and ultimately want the additional information about the booking / company. By using the prequery to get all the preliminary qualifying ad inserts and the last stamped invoice for a company, it can THEN be joined to the invoices by that invoice ID and timestamp.

I didn't get the "How many Days Ago" you had, but hopefully you have all the information you'll need and can recompute once you confirm this query does the job (or most of it). Since I had a direct link to a single invoice, instead of concatenating the fields into a single field, I just left them as individuals... again, you can adjust as needed.

SELECT STRAIGHT_JOIN
      PreQuery.Company_ID,
      PreQuery.Company_Name,
      PreQuery.Run,
      PreQuery.Bill_Freq,
      PreQuery.Currency,
      PreQuery.Rate,
      PreQuery.Publication_ID,
      PreQuery.Booking_ID,
      PreQuery.Art_Stamp,
      PreQuery.Title,
      Invoices.prefix, 
      Invoices.invoice_id, 
      Invoices.suffix,
      Invoices.invoice_stamp,
      Invoices.reference 
   from 
      ( SELECT 
              ab.company_id,
              c.Company_Name
              ab.run,
              ab.bill_freq,
              ab.currency,
              ai.publication_id,
              ai.booking_id,
              format( ai.rate, 2 ) Rate,
              p.title,
              p.art_stamp,
              ( select MAX( invoices.invoice_stamp ) 
                    from Invoices 
                    where Invoices.Company_ID = c.Company_ID ) As LastInvoiceStamp;
           from
              adinserts ai
                 JOIN publications p
                    ON ai.publication_id = p.publication_id
                    and p.publication_stamp >= UNIX_TIMESTAMP('2010-01-01 00:00:00'

                 JOIN adBookings ab
                    ON ai.Booking_ID = ab.Booking_ID

                    JOIN Company c
                       ON ab.Company_id = c.Company_ID

           where
                  ai.Invoice_ID is null
              and ai.Cancel_Stamp is null
              and ai.rate > 0 ) PreQuery

      LEFT JOIN Invoices
         ON PreQuery.Company_ID = Invoices.Company_ID
         AND PreQuery.LastInvoiceStamp = Invoices.Invoice_Stamp

You may need to apply a COALESCE() to the invoices fields in case null where no prior invoice FOR the company, but I think this is very CLOSE TO what you are looking for.

Ok here's another method that appears to return what I want. Although it causes me physical pain to have to resort to something like this...

SELECT `adbookings`.`company_id`, `companies`.`company_name`, `adbookings`.`run`, `adbookings`.`bill_freq`, `adbookings`.`currency`, FORMAT(`adinserts`.`rate`, 2) AS `rate`, `publications`.`title`, 
CONCAT(FLOOR(CAST(UNIX_TIMESTAMP()-SUBSTRING_INDEX(`invoices`.`last_invoice`, '-', 1) AS SIGNED INTEGER)/86400), ' days ago') AS `last_invoice_days_ago`, 
SUBSTRING_INDEX( SUBSTRING_INDEX(`invoices`.`last_invoice`, '-', 3), '-', -1) AS `last_invoice_no`, 
SUBSTRING_INDEX( SUBSTRING_INDEX(`invoices`.`last_invoice`, '-', 4), '-', -1) AS `last_invoice_reference` 
FROM `adinserts` 
INNER JOIN 
(
SELECT `publications`.`publication_id`, `publications`.`art_stamp`, `publications`.`title`
FROM `publications`
LEFT JOIN `adinserts` ON `publications`.`publication_id`=`adinserts`.`publication_id` AND `adinserts`.`invoice_id` IS NOT NULL
WHERE `publications`.`publication_stamp`>=UNIX_TIMESTAMP('2010-01-01 00:00:00')
GROUP BY `publications`.`publication_id`
HAVING COUNT(`adinserts`.`invoice_id`)>0
) AS `publications` ON `adinserts`.`publication_id`=`publications`.`publication_id`
LEFT JOIN `adbookings` ON `adinserts`.`booking_id`=`adbookings`.`booking_id` 
LEFT JOIN
(
SELECT `company_id`, MAX(CONCAT_WS('-', `invoice_stamp`, `invoice_id`, CONCAT_WS('', `prefix`, `invoice_id`, `suffix`), `reference`)) AS `last_invoice` 
FROM `invoices`
GROUP BY `invoices`.`company_id`
) AS `invoices` ON `adbookings`.`company_id`=`invoices`.`company_id`
LEFT JOIN `companies` ON `adbookings`.`company_id`=`companies`.`company_id` 
WHERE `adinserts`.`invoice_id` IS NULL AND `adinserts`.`cancel_stamp` IS NULL AND `adinserts`.`rate`>0
ORDER BY `publications`.`art_stamp`, `companies`.`company_name`

So in the 2nd derived table I'm running concatenating the data I want from that particular row, then running MAX() on that.
Then in the parent query I'm using the substring function to "explode" based on my delimeter.

This is a horrible, horrible method to have to resort to within a relation database :(
But it works :)