join table or cte

Question

First off, I've tried to find a suitable solution on the internet, but I really didn't find one that worked for me. What seems to be my problem? Well...

At my new job we use MySQL. I'm used to working with DB2 and SQL Server so I was very, very surprised that using common table expressions (cte) or properly joining a table doesn't work. For example while creating a (sub) table as second from-statement you can't use any of the data from the first from-statement to connect the two tables. What does work is that you use the first from-statement again in the (sub) table, but, when you add a third table that has to use the (sub) table result of the second from-statement, it just gives me an error. :-(

to simplify my question. I have a table with customer data. Each customer is unique, I use customer number and name. I also have a invoice table with multiple invoices for each unique customer and the date of those invoices. And last I have a revenue table that has all revenue data, multiple lines are possible, there is a date when revenue is booked.

Now (and again this is a simplified example) I want to get the max date from the invoices table and use that date to get only the revenue after that date. And as result of the whole query get customer number, customer name, max invoice date, sum of revenue.

I used to do the max date stuff with either a common table expression or a (sub) table in the from-statements and then use this in the third from-statement revenue date > max invoice date. But how do I get this to work within MySQL? And this is a simple example, but how do you do stuff like this when it gets more complex?

Any help will be much appreciated! Thanks.

Example query:

select * 
from table_A t1 
  join table 
    ( select t0.customernumber, max(t0.date) as max_date 
      from table_B t0 
      where t1.customernumber = t0.customernumber
    ) t2 
    on  t1.customernumber = t2.customernumber 
  join table_C t3 
    on  t1.customernumber = t3.customernumber
    and t3.date > t2.max_date

Answer 1

Without table structure, it's not easy to write code but here is a simplified query:

SELECT 
    c.*, i.*,
    r.max_date
FROM 
    customer AS c
  LEFT JOIN                                        -- or just JOIN 
    ( SELECT customernumber, 
             MAX(revenue_date) AS max_date
      FROM revenue
      GROUP BY customernumber
    ) AS r
      ON  r.customernumber = c.customernumber
  LEFT JOIN                                        -- or just JOIN
    invoice AS i
      ON  i.customernumber = c.customernumber
      AND i.invoice_date >= r.max_date ;

The above is standard SQL and will work in MySQL as in all other DBMS. For more complex situations though, where LATERAL or similar syntax can be used to provide more elegant (or more efficient) SQL, MySQL is not helping. It lacks many of the standard SQL syntax (like common table expressions, table value constructors, window functions, LATERAL, CHECK constraints and probably some other I forget).

If you want to simulate (SQL-Server's) OUTER APPLY, (the ISO/ANSI) LATERAL or (DB2's) TABLE syntax, it becomes (bad news) quite complicated but (good news) usually quite efficient with proper indexing. This is mainly useful when you have a small "base" table (the customer in this case) or you want to limit the query to only a few customers with a WHERE clause. While the query above with the GROUP BY will have to find the MAX(revenue_date) for all customers (and that means a full table or a full index scan in revenue), the following one will only find the max for the few selected customers (so a few index seeks):

SELECT 
    c.*, i.*,
    r.revenue_date AS max_date
FROM 
    customer AS c
  LEFT JOIN
    revenue AS r
      ON  r.customernumber = .customernumber
      AND r.PK = 
          ( SELECT ri.PK
            FROM revenue AS ri
            WHERE ri.customernumber = c.customernumber
            ORDER BY ri.revenue_date DESC
              LIMIT 1
          ) 
  LEFT JOIN                                        
    invoice AS i
      ON  i.customernumber = c.customernumber
      AND i.invoice_date >= r.revenue_date 
WHERE
    <some conditions on the customer table> ;

• An advantage is that you can have any column of the revenue table in the final SELECT list.
• There are several improvements/variations, like if the (customernumber, revenue_date) is unique, you can use the revenue_date instead of the primary key for the join.
• If you don't want one row only but several (a TOP n query), it gets quite complicated but can still be done.