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

Question

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 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?

Quick question... about the invoice_ID.. I would assume this is an auto-incrementing column, and that in itself would identify itself as the "most recent" instead of a date/time... would that be correct? Or, if there are other invoices that may be older (even a day or so) and THEY get UPDATED / REVISED, the new timestamp takes precidence... Please clarify. — DRapp, May 27 '11 at 12:38
Good question - you are right to ask for clarification. Yes invoice_id is an auto increment field. Invoices belong to a particular company and in 95% of cases, using the invoice_id would be correct but the invoice_stamp will be true for 100% of cases. We sometimes have clients who want a particular date/month on an invoice so that prevents the invoice_id column being correctly ordered by date. If I did MAX(invoice_id) I would still have the problem of not being able to get the invoice_stamp field of that MAX(invoice_id) record. — batfastad, May 27 '11 at 12:52

DRapp · Answer 1 · 2011-05-27T14:54:51.117

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.

adinserts is joined to 3 tables... publications (publication_id), adbookings (booking_id) and invoices (invoice_id). adbookings is joined to companies (company_id). invoices is joined to companies (company_id). The query is to find adinserts which still need to be invoiced (invoice_id IS NULL), are not cancelled, and have a rate>0. The first derived table within the INNER JOIN is to restrict this to publications that have at least one adinsert invoiced (invoice_id IS NOT NULL). So adinserts belonging to publications that haven't started the invoicing phase won't be returned. — batfastad, May 27 '11 at 13:36
@batfastad, yes, I do understand that... that was the second part "In addition". were those to be LEFT JOINS (optionally found) too, or NORMAL joins (Join IDs MUST be found)... — DRapp, May 27 '11 at 13:38
So basically I'm trying to get adinserts that need to be invoiced, from publications where invoices have started to be raised. And my 2nd derived table (the one with my GROUP BY problem) is just to display some extra information from a table related to the parent company, showing the last invoice raised for that company. I know it seems overly complicated but it's the only way I've found so far to get the records I want. — batfastad, May 27 '11 at 13:39
@batfastad, much better clarification in comment.. posting that in your question would help others, and I'll continue to ponder a workable solution for you... — DRapp, May 27 '11 at 13:40
@DRapp well you are correct though. The 2nd join (LEFT JOIN adbookings ON adinserts) could just be a regular JOIN... as the adinsert will always have a parent adbooking record. And the 4th join (LEFT JOIN companies ON adbookings) could also be a regular JOIN... as the adbooking will always have a parent company record. — batfastad, May 27 '11 at 13:43
@batfastad, the adinserts... will that be a 1 to many to the publications? OR 1 adinsert = 1 publication ID? — DRapp, May 27 '11 at 14:00
@DRapp 1 adinsert = 1 publication ID. 1 booking will have multiple adinserts. 1 company may have multiple bookings. — batfastad, May 27 '11 at 14:15
@batfastad, completely revised answer with query. Please review, check it out. — DRapp, May 27 '11 at 14:55

score 0 · Answer 2 · answered May 27 '11 at 22:08

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 :)

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

2 Answers2