I recently got some help with this, but I've evidently screwed something up.
I have a query to get a single customer from my customer table. Next, I have a query that looks at all animals in a lookup table, then checks against a customer_animals table to see if that animal is selected. I use this to check or uncheck a checkbox next to the animal name in a detail section for my customer. All animals show up as checkboxes - which is what I need. But I only want to have the checkbox checked if that animal_id exists in the customer_animals table for this customer_id.
What's happening is that because the query is joined using animal_id, it repeats the animal for however many entries in my table have that particular animal_id - it's not limiting to the single customer that is selected. So instead of having a checkbox for each animal that exists in the lookup_animal table, I have checboxes for how many times that animal_id shows up in the customer_animals table.
The customer_animals table has 3 columns - id, animal_customer_id, and animal_id.
The lookup_animal table has 2 columns - animal_id and animal_name.
The only thing I'm really using out of the customer table is the id field.
The code for this thing is below. Thanks in advance for any guidance.
<!--- QUERY TO GET A SINGLE CUSTOMER --->
<cfquery name="edit_this_cust" datasource="#DSN#">
select * from customer where id = <cfqueryparam value="#FORM.customer_id#" cfsqltype="cf_sql_integer">
<!--- QUERY TO LOOKUP ALL ANIMALS, THEN SEE IF THEY EXIST FOR THIS CUSTOMER --->
<!--- GET ALL ANIMALS --->
<cfquery name="get_animals" datasource="#DSN#">
select la.animal_id, la.animal_name,
CASE WHEN ca.animal_id IS NULL THEN 0
END AS IsSelected
from lookup_animal la
LEFT JOIN customer_animals ca ON la.animal_id = ca.animal_id
order by animal_name asc
<!--- THIS IS THE CODE FOR THE CHECKBOXES --->
<input name="animal_id" type="checkbox" value="#animal_id#" <cfif IsSelected>checked="checked"</cfif> /> #animal_name#<br />