MySQL Multiple Joins With Distinct Order By and Limit

Question

I've been searching all over for this and either it's a pipe dream of mine and doesn't exist or I'm not using the right search terms. Everything I know about SQL is self-taught, and what I do with it is generally very simplistic, so what may be intuitive and simple to you isn't necessarily to me.

I'm wondering if you can use multiple join statements in MySQL with different results, something like:

SELECT client.firstname, client.lastname, service.date,
payment.date, payment.amount
FROM client
JOIN service ON client.id = service.clientid
ORDER BY service.date DESC
LIMIT 8 
JOIN payment ON client.id = payment.clientid
ORDER BY payment.date DESC
LIMIT 2;

I'm trying to get output showing the client's last couple of monthly payments and the last couple of months weekly service to show at a glance if the client using the service is up on their monthly payments.

Thanks in advance for any insights you can give me.

Answer 1

It looks like you're joining really funky, or you're trying to use a union.

Limit (fixed)

SELECT client.firstname, client.lastname, service.date,
payment.date, payment.amount
FROM client
JOIN service ON client.id = service.clientid
JOIN payment ON client.id = payment.clientid
ORDER BY service.date DESC, payment.date DESC
LIMIT 8;

Union

SELECT client.firstname, client.lastname, service.date,
payment.date, payment.amount
FROM client
JOIN service ON client.id = service.clientid
JOIN payment ON client.id = payment.clientid
ORDER BY service.date DESC
LIMIT 8
UNION ALL
SELECT client.firstname, client.lastname, service.date,
payment.date, payment.amount
FROM client
JOIN service ON client.id = service.clientid
JOIN payment ON client.id = payment.clientid
ORDER BY payment.date DESC
LIMIT 2;