I am trying to get a list of clients from my clients table for a particular care planner (given the $careplanner_id from the clients table) who have not been assessed. So in the assessments table there is no record with those particular client_id’s.
I also want to get a list of clients who have been assessed, so their client_id’s do show up in the assessments table.
This is what I tried even though I knew it wouldn’t work. And sure enough … it gave me an SQL syntax error.
"SELECT clients.id AS client_id, clients.first_name, clients.last_name
FROM clients
WHERE clients.careplanners_id = $user_id
JOIN assessments ON assessments.client_id IS NOT NULL
ORDER BY clients.last_name ASC"
and
"SELECT clients.id AS client_id, clients.first_name, clients.last_name
FROM clients
WHERE clients.careplanners_id = $user_id
JOIN assessments ON assessments.client_id IS NULL
ORDER BY clients.last_name ASC"
I’m positive that’s not a correct way to use a JOIN, but how can I get the results I am looking for?
Your JOIN’s ON clause needs to define the relationship between the two tables (I also think it needs to come before the WHERE clause):
SELECT clients.id AS client_id, clients.first_name, clients.last_name
FROM clients
JOIN assessments ON assessments.client_id = clients.id
WHERE clients.careplanners_id = $user_id
ORDER BY clients.last_name ASC
If there are no matching records in the assessments table, the query won’t return any results.
To get those clients that have not been assessed, you can do:
SELECT clients.id AS client_id, clients.first_name, clients.last_name
FROM clients
LEFT JOIN assessments ON assessments.client_id = clients.id
WHERE clients.careplanners_id = $user_id AND assessments.client_id IS NULL
ORDER BY clients.last_name ASC
You could also change the SQL slightly to get both sets of information with one query, seeing which ones have been assessed by checking (in PHP) which records have a NULL value for the assessment_id:
SELECT clients.id AS client_id, clients.first_name, clients.last_name, assessments.id AS assessment_id
FROM clients
LEFT JOIN assessments ON assessments.client_id = clients.id
WHERE clients.careplanners_id = $user_id
ORDER BY clients.last_name ASC
Thank you! That makes a lot of sense. I’m going to try your last option in my controller, because the end result will be forming a dropdown list of the unassessed clients and another dropdown list of the assessed clients.
Just thought I’d mention it worked like a charm, and using this query I was able to roll the two methods and another method that was calling for all client names assigned to a care planner into only one method in the model.