Nested Queries

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. :slight_smile:

SQL clauses do not have underscores.

Oops. I knew it had to be something like that. Thanks. :blush: The CodeIgniter query builder uses underscores, but I tend to not use that, just straight mySQL for the more complicated queries.

i think the query isn’t doing what you think it’s doing

the subquery in the SELECT clause wants to return only the latest careplanner_id

however, the join in the FROM clause will return all careplanner_clients for each client, and since your SELECT clause is basically returning only client info, you’re going to get duplications

is this what you want?

No it isn’t. I’m working on fixing that one right now. I’d rather not use a JOIN.

so in this case, include it out

Sorry, what do you mean by ‘include it out’?

do not include the join to the careplanner_clients table

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.