Nested Queries

[WebMachine]

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.

[TomB]

ORDER_BY

SQL clauses do not have underscores.

[WebMachine]

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

[r937]

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?

[WebMachine]

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

[r937]

I'd rather not use a JOIN.

so in this case, include it out

[WebMachine]

include it out

Sorry, what do you mean by 'include it out'?

[r937]

do not include the join to the careplanner_clients table