[RESOLVED] 2-table query where result must match multiple options

bunner_bob

I'm having a little trouble figuring out a query.

I have three tables: widgets, categories, and widgetsCategories (which records which widgets belong to which categories, in a many-to-many relationship). We can ignore the categories table for this discussion - all we need are widgets and widgetsCategories.

Some example data:

//widgets:

widgetId     title
1            Hammer
2            Saw


//categories:

categoryId     title
1              Tools
2              Green Things


//widgetsCategories:

widgetCategoryId     widgetId     categoryId
1                      1             1
2                      2             1
3                      1             2

So both the hammer and saw are tools, but only the hammer is a green thing

It's easy enough to find all the widgets for a given category, or all the categories for a given widget, but what about trying to find all the widgets that belong to several/all specified categories? In other words, I want to select all widgets that are both tools AND green things.

I tried:

SELECT widgets.* from widgets, widgetsCategories WHERE widgets.widgetId=widgetCategories.widgetId AND widgetCategories.categoryId=1 AND widgetCategories.categoryId=2

but obviously that doesn't work - I get nothing.

What's the proper way to do this sort of query? Do I need to do multiple queries or is there a straightforward way to do this?

Piranha

You need to use having:

select columns
from wid
join widcat on wid.id = widcat.widid
where widcat.catid = 1 or widcat.catid = 2
having count(wid.id) = 2;

The having part should have the amount of categories that should match.

bunner_bob

Hmm - not working for me. My query (pardon the change in table/col names - these are the real names):

SELECT entries.entryId FROM entries JOIN entriesEthics ON entries.entryId=entriesEthics.entryId WHERE entriesEthics.ethicsId=1 OR entriesEthics.ethicsId=2 HAVING count(entries.entryId) = 2

I get zero results. When I drop the "having" I get 3 results - two of which are the same. This is expected. There are two "entries" - one of which belongs to both ethics categories, the other of which only belongs to one category. I should be getting the one entry that belongs to both categories.

Did I mistype something?

Piranha

My fault, I missed that you should group as well:

select columns
from wid
join widcat on wid.id = widcat.widid
where widcat.catid = 1 or widcat.catid = 2
group by wid.id
having count(wid.id) = 2;

That is what I get for posting a query without testing it first...

[edit]
Is the query you posted last the real query, do you only want the entryid? In that case you should not join, only use the entriesEthics table.
[/edit]

bunner_bob

Yeah! Woo hoo! That did it perfectly.

I had whittled down the previous "real" query because it wasn't getting me what I wanted - thought I might be asking for too much in the SELECT so I changed it to just the id. But actually I do want entries.*, so the way you present it works perfectly.

My deepest thanks.

P.S. - I may be back when I start getting into joining additional tables. There are two more tables I need to add into the search. But I think you've given me enough of a headstart here - we shall see.

Piranha

If you can't get it working with additional tables don't hesitate to ask again. But do it in a new thread, there is a big risk people won't check new posts in an old thread.

bunner_bob

It did get worse, and I did another post here. Curious for your take on whether Lars' idea (2nd response) would be slow due to using correlated subqueries? Probably won't be an issue until the database gets huge, but worth asking.
It works great now, but we only have a few records in the db.