I was asked recently to create a searchable database for a number of publications and research documents that live on our website. The content is all static pages, but there’s getting to be a considerable number of them (hundreds) so the users end up scrolling and reading through all these titles to find what they need. So I’ve made the index page a search form where you can choose various criteria (categories, document types) for your search.
I have an SQL query that I use to build the search form (a bunch of SELECT elements, because the content owner preferred the look of that to checkboxes), and for any of the search categories you can choose “ALL” and get all the document types or categories.
It’s very common for a user to choose a document type, and leave “ALL” for the remaining criteria. But as you start to narrow down your results, the possibility of your search coming up empty becomes more likely. So I’d like to remove the criteria options with zero results when someone searches on a document type. I’ve been able to do this in the past by joining all the tables together with inner joins, but I this time it’s not working, and I think it’s because of how I have my database set up.
The database consists of:
documents table (cc_docs)
doc_id
doc_title
doc_desc
doc_url (the folder where the publication resides)
Each document has a doctype: (cc_types). Each document can have more than one type.
type_id
type_name
A table joining those two (cc_doctype)
dt_id (I put an autonumber ID column on everything)
doc_id (joined to the docs table)
type_id (joined to the types table)
Finally, each document can have many categories. The categories are arranged in a parent/child category table (cc_categories).
cat_id
cat_name
cat_desc
parent_cat
Top level categories have a null parent_cat. Nothing gets assigned to parents categories.
To join the categories to docs, there’s another table (cc_doccat)
dc_id
doc_id (joined to the docs table)
cat_id (joined to the categories table)
The SQL to build the form is fairly simple.
SELECT cc_categories.cat_id AS parent_id
, cc_categories.cat_name AS parent_name
, cc_categories.cat_desc AS parent_desc
, sub.cat_id AS sub_id
, sub.cat_name AS sub_name
FROM cc_categories
LEFT JOIN cc_categories AS sub
ON sub.parent_cat = cc_categories.cat_id
WHERE cc_categories.parent_cat IS NULL
ORDER BY cc_categories.cat_name, sub.cat_name
This lets me build a nice form with some labels and drop-downs. My next step was to join the other tables, but if I use inner joins (below) my criteria elements vanish.
SELECT cc_categories.cat_id AS parent_id
, cc_categories.cat_name AS parent_name
, cc_categories.cat_desc AS parent_desc
, sub.cat_id AS sub_id
, sub.cat_name AS sub_name
FROM ( ( ( cc_categories
LEFT JOIN cc_categories AS sub
ON sub.parent_cat = cc_categories.cat_id
)
INNER JOIN cc_doccat
ON cc_doccat.cat_id = cc_categories.cat_id
)
INNER JOIN cc_doctype
ON cc_doctype.doc_id = cc_doccat.doc_id
)
INNER JOIN cc_types
ON cc_types.type_id = cc_doctype.type_id
WHERE cc_categories.parent_cat IS NULL
ORDER BY cc_categories.cat_name, sub.cat_name
And if I use left joins of course I get the same results as not joining the tables at all, until I filter for document type with the WHERE clause, at which point I also lose my categories.
I’m thinking that I need a more complex query due to how my categories table is set up, but I’m not exactly sure where to go from here.