Checkbox Help Needed

I'd do it with 3 queries. The first two would select the locations and categories, and the third would select the checked items.

Joe Science wrote:
> I need to be able to query and output each location with all five categories


This is one of the few legitimate uses for a CROSS JOIN. Cross joins produce a deliberate cartesian product. ie The number of records in the first table multiplied by the number of records in the second table. That is often undesirable, as it can produce an extremely large amount of information. However, in your case a CROSS JOIN should produce the desired results: return all combinations of categories and locations.

SELECT l.location, c.category
FROM location l CROSS JOIN category c
ORDER BY l.location, c.category

> and show the checked categories for each

You can use an OUTER JOIN with the location_categories table to determine which items were checked. If the location_categories id is NULL, then the category was not checked. Use a CASE statement to return 1 or 0 indicating the checked state. Then cfoutput the query as usual.

SELECT
l.location, c.category,
CASE WHEN lc.locationID IS NULL THEN 0 ELSE 1 END AS WasChecked
FROM location l CROSS JOIN category c
LEFT JOIN location_categories lc
ON l.locationID = lc.locationID AND c.categoryID = lc.categoryID
ORDER BY l.location, c.category

Bear in mind CROSS JOINs are not suitable for large tables. A cross join on two tables containing 1000 records each would produce a result of 1 million records. But in your case the tables are small. So the cross join would produce only 100 records. That said, use cross joins with care.

ie
locations (20 rows) x categories (5 rows) = result (100 rows)