This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC 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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "i Watch "i New topic
Author

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

Andy Westley
Greenhorn

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.id = 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

SELECT DISTINCT thing.*
FROM thing, thing_category AS A, thing_category AS B, thing_category AS C
WHERE thing.id = 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: http://aspose.com/file-tools
 
subject: i'm sure there must be a group by in there...
 
Similar Threads
Hibernate one-to-many inner join?
How to create a dynamic index page
populating the data from array list
EJB - Everything works except persist()
extracting some elements from ArrayList on JSP