I am still very inexperienced when it comes to nested mySQL nested queries (and JOINS), and have again hit a brick wall.
In this query, I am trying to get data from the clients table as well as just the most recent careplanner assigned to each client from the careplanners_clients junction table (client_id, careplanner_id). The careplanner result could possibly be empty for a client. Here is my attempt:
SELECT clients.first_name,
clients.last_name,
clients.account_num,
clients.guidelines,
clients.guidelines_signed,
clients.signed_date,
clients.case_status,
(SELECT careplanners_clients.careplanner_id
FROM careplanners_clients
ORDER_BY careplanners_clients.created_at DESC
LIMIT 1) AS careplanner_id
FROM clients
JOIN careplanners_clients
ON careplanners_clients.client_id = clients.id
WHERE clients.community_id = " . $community_id . "
AND (clients.guidelines = 'no'
OR clients.guidelines_signed = 'no')
I’m getting a syntax error (“near ‘careplanners_clients.created_at DESC LIMIT 1) AS careplanner_i’ at line 10”) which I can’t see. What went wrong here?
If it’s a simple typo, I’ll thank you and then go hide in the nearest hole.