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