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.