File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes i'm sure there must be a group by in there... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "i Watch "i New topic

i'm sure there must be a group by in there...

Andy Westley

Joined: Nov 09, 2004
Posts: 19
I have 3 tables - Thing, Category and Thing_Category.

Thing - id, name
Category - id, name
Thing_Category - thing_id, category_id

Actually I have some similar but more complicated tables, but I'm trying to keep this simple. My application is going to search this data and return things that are in categories. I've got a search form where you can pick all the categories that you want your search to filter on. The problem I'm having is that I want to parameterise the type of the search with an AND/OR. Depending on how an input field is poplulated I want to retrieve all things that feature any of the categories - the "OR", and I've got that covered.

select thing.*
from thing, thing_category
where = thing_category.thing_id
and (thing_category.category_id = X) or (thing_category.category_id = Y) or (thing_category.category_id = Z)

It's the "AND" I'm having trouble with. I want to retrieve a list of things match all of the categories. Can anyone suggest a way of picking out all the thing_ids from the thing_category table that match at least a variable number of categories?
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
I think there a few answers to your question. The "goodness" of the answer for you depends on your needs and data I would say.

Here is one idea

FROM thing, thing_category AS A, thing_category AS B, thing_category AS C
WHERE = A.thing_id
AND A.thing_id = B.thing_id
AND B.thing_id = C.thing_id
AND A.category_id = X
AND B.category_id = Y
AND C.category_id = Z

So basically a couple of self joins but these can be expensive. And "dynamic" it isn't.

But it will do what you want.

Other than that some inner queries of some nature I think will be required.
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333

"in" is approprate and effective when the list is short; it's generally not appropriate when the list is long. Many databases have a hard limit on the size of the list.

If the category list is being generated dynamically from the database, you can sometimes roll two queries into one (and also be effective with much larger lists), using something like:
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Oops, reading comprehension problem on my part, missed the AND bit, let's try this again...

If your DB doesn't play nice with "having", then the alternate syntax is
I agree. Here's the link:
subject: i'm sure there must be a group by in there...
It's not a secret anymore!