Hiding categories that contain no results

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. :confused:

first of all, thanks for the detailed explanation – i wish more people did this when asking questions

you lost me right here

oh, FFS!!!

i hope you declare a composite UNIQUE index on the other two columns

criteria elements? what are these?

also, why do you force evaluation of joins using parentheses?

(oh, wait… is this MS Access?)

try this –

SELECT ...
  FROM cc_categories
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
LEFT OUTER
  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

Hah, well I guess my detailed explanation was still lacking in some ways.

Say I choose “Case Studies” for a document type, and click submit. This returns 15 documents.

For categories, I have things like this:

Transportation Mode (parent category)

  • Car
  • Bike
  • Rail
  • Bus
  • Aviation
  • Other

Location

[list of US states]

Climate Impacts

  • Wind
  • Storm
  • Sea level rise
  • Storm Surge
  • Temperature

etc…

Out of those 15 results, there may be zero that are in Kentucky, so if I chose that state, I get zero results. In fact, those 15 results might only occur in 9 states, so I’d like to be able to hide the rest. My select drop-down would only contain the states that have documents associated with them.

Yes. Someone killed our SQL Server setup Monday night, so nothing SQL-related is working unless it’s using Access. Although the parens seem to work fine in SQL Server too.

When I do that and load the page (without submitting the form) I only see the document type dropdown. The categories are all hidden. Same if I search on a doctype.

Could this be because none of the parent categories have any documents assigned, and since the parents are hidden, so are the subs?

you realize that i can’t see your dropdown, or whatever else is going on in your app

how about testing your queries directly in the database, i.e. not driving your app

i can help you with sql, but not application behaviour

If I cfdump the query, the categories (sub and parent) are all null.

But in doing this I realized that I was doing a separate query to get the list of document types (left over from a previous version of the form).

If I pull the list of document types to the query above, they come up null as well.

I need to take another look at this to figure out why my query is coming up with an empty result set when no search parameters are given.

Ok, I’m now much closer. I have the result set mostly the way I want it with this:

SELECT    cc_docmod.doc_id AS docid
	, cc_types.type_name
	, cc_types.type_id
        , parents.cat_id AS parent_id
	, parents.cat_name AS parent_name
        , cc_categories.cat_id AS sub_id
        , cc_categories.cat_name AS sub_name
FROM ( ( ( ( cc_docs
INNER JOIN cc_doctype
	ON cc_doctype.doc_id = cc_docs.doc_id
        )
INNER JOIN cc_types
	ON cc_types.type_id = cc_doctype.type_id
        )
INNER JOIN cc_doccat
	ON cc_doccat.doc_id = cc_docs.doc_id
        )
INNER JOIN cc_categories
	ON cc_categories.cat_id = cc_doccat.cat_id
        )
LEFT OUTER JOIN cc_categories AS parents
	ON parents.cat_id = cc_categories.parent_cat
WHERE parents.parent_cat IS NULL
ORDER BY cc_docmod.doc_id, cc_types.type_id, parents.cat_id, cc_categories.cat_id

I decided to write the query as if I were making a list of all the documents in the database, then added categories and document types, then just removed from the SELECT statement the things I didn’t need. I ended up changing how the parents/children were joined.

I have what I’m looking for, but I have dupe results, so I think I have to handle that in my presentation code. Or maybe fiddle with the ORDER BY.

Ok, this solution works for the categories:

SELECT    cc_types.type_name
	, cc_types.type_id
        , parents.cat_id AS parent_id
	, parents.cat_name AS parent_name
        , cc_categories.cat_id AS sub_id
        , cc_categories.cat_name AS sub_name
FROM ( ( ( ( cc_docs
INNER JOIN cc_doctype
	ON cc_doctype.doc_id = cc_docs.doc_id
        )
INNER JOIN cc_types
	ON cc_types.type_id = cc_doctype.type_id
        )
INNER JOIN cc_doccat
	ON cc_doccat.doc_id = cc_docs.doc_id
        )
INNER JOIN cc_categories
	ON cc_categories.cat_id = cc_doccat.cat_id
        )
LEFT OUTER JOIN cc_categories AS parents
	ON parents.cat_id = cc_categories.parent_cat
WHERE parents.parent_cat IS NULL
ORDER BY parents.cat_id, cc_categories.cat_id

The problem now (and I’ve had it before) is that I get duplicate document types, because with CF I can only group by so many things before I get dupes. So I’m probably going to do two queries - one for the doc types and one for the categories.

that’s the best way to handle a situation where there are multiple many-to-many relationships

Thanks rudy! :slight_smile: