Known Participant

Question

SQL Help

Forum|Forum|17 years ago
February 2, 2009
11 replies
1858 views

When using "IN" in a select statement is there a way to make sure all items match?

So if you say:

id IN (1,2,3)

I want to get all records that match all 3, if it only matchs 2 of the 3 it wouldn't be returned but if it matched all 3 and a fourth it would return it.

I'm not even sure how to phrase this question... But maybe if someone gets the jist of what I'm trying to say they can help me figure this out...

Thanks

Database access

This topic has been closed for replies.

D

dbldutchAuthor

Known Participant

Thank You, that works, but it taks 66 seconds to run. I'm wondering if I have my logic screwed up and there is a better way to get the results I'm looking for.

K

Kronin555

Participating Frequently

Well, you can actually get rid of your join now. You're not doing anything with the resourcerelationship table anymore.

<cfquery name="qGetResources" datasource="#application.settings.db#">
SELECT r.resourceID, r.handle, r.title, r.description
FROM resource AS r
WHERE <cfif Len(url.topic)>
r.resourceID IN (
SELECT resourceID
FROM resourceRelationship
WHERE resourceTopicID IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#url.topic#" list="yes" separator=",">)
GROUP BY resourceID HAVING count(*) = #listlen(url.topic)#
) AND
</cfif>
r.approved = <cfqueryparam cfsqltype="cf_sql_bit" value="1"> AND
r.active = <cfqueryparam cfsqltype="cf_sql_bit" value="1">
GROUP BY r.resourceID
ORDER BY r.titleSort ASC;
</cfquery>

If that's still running slow, you're going to need to do some database tuning. Make sure you have appropriate indexes, etc. I'm not sure how fast MySQL is at doing IN and HAVING operations, so I have no basis for comparison.

D

dbldutchAuthor

Known Participant

Is that solution going to limit me to only limit by topics? Like I said, we may be limiting by other thinks in the future.

When using HAVING COUNT() = 3 would this include items that have 3 topics but only two of the ids in the IN?

This is obvioiusly the most complex query I've ever tried, so that's why I'm asking a bunch of questions. Thanks for your help.

K

Kronin555

Participating Frequently

This _should_ work, but I'm a little iffy on my <cfqueryparam list="yes"..> line.

<cfquery name="qGetResources" datasource="#application.settings.db#">
SELECT r.resourceID, r.handle, r.title, r.description
FROM resource AS r
JOIN
resourceRelationship AS rr
ON r.resourceID = rr.resourceID
WHERE <cfif Len(url.topic)>
r.resourceID IN (
SELECT resourceID
FROM resourceRelationship
WHERE resourceTopicID IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#url.topic#" list="yes" separator=",">)
GROUP BY resourceID HAVING count(*) = #listlen(url.topic)#
) AND
</cfif>
r.approved = <cfqueryparam cfsqltype="cf_sql_bit" value="1"> AND
r.active = <cfqueryparam cfsqltype="cf_sql_bit" value="1">
GROUP BY r.resourceID
ORDER BY r.titleSort ASC;
</cfquery>

D

Dan_Bracuk

Inspiring

Read Kronin's most recent answer. It's the best one. I slapped myself on the forehead and said, "doh" when I read it.

D

dbldutchAuthor

Known Participant

I will most likely never be displaying ALL of the items unless for some reason every resource is about a certain topic. Right now I'm just using topic as the example to limit the resources by but there may be other options in the future to limit these by.

I believe I have all of the primary keys indexed. I've got 5009 resources and 20 topics that each resource has 1 to 5 of.

Here is the simplest query that I'm using that would get ALL of the results. It has an EXECUTIONTIME of 188.

Shown below #1

Limiting query based on topics that the user wants to view. Remember, I'm wanting it to match only the resources that have 1) ALL the topics related to it and all resources that meet #1 and have additional topics. This query took 53456.

Shown below #2

The above query does exactly what I'm looking for, it just takes forever. My test above is when someone simply uses 1 topic.

Thank you for taking your time to look through this.

D

Dan_Bracuk

Inspiring

Is it your intent to always display 100% of your data, or will there be some sort of user input?

K

Kronin555

Participating Frequently

select resource.title
from resource join resourcerelationship on (resource.resourceid = resourcerelationship.resourceid)
where
resourcerelationship.resourcetopicid in (1, 2, 3)
group by resource.title
having count(resourcerelationship.resourcetopicid) = 3

This will work. That last 3 is the number of topics that were picked (the list length of the topic IDs being put in the IN check).

D

Dan_Bracuk

Inspiring

Where exactly are these thousand resources and 20 topics going to come from?

D

dbldutchAuthor

Known Participant

They'll all be stored in my mysql database. I just added 5000 records and then randomly had each new resource have 1 to 5 topics and it takes 53 seconds to execute.

Thanks for your willingness to ask questions and help.

K

Kronin555

Participating Frequently

Please post the full query you're using and we'll take a look at it.

Do you have appropriate indexes on the tables you're joining?

D

Dan_Bracuk

Inspiring

select whatever
from your three table with proper joins

where
resourceid in (select resourceid
from resourcerelationship
where resourcetopicid = 1)
and same thing for 2 and 3

D

dbldutchAuthor

Known Participant

Dan, thanks that works great. Now when I get a thousand resources and 20 topics is the performance just going to be awful or is this the best solution?

Thank you very much!

D

dbldutchAuthor

Known Participant

I knew that didn't make any sense... I'm having a hard time even phrasing it so here it goes... the project I'm working on has multiple resources and topics that describe each of the resources. Each resource could have multiple topics and each one can be different. There will be a third table that relates the resources to the topics. (I kept the tables below as simple as possible.)

resource
resourceID, title
1, Resource 1
2, Resource 2
3, Resource 3
4, Resource 4

resourceTopic
resourceTopicID, title
1, Topic 1
2, Topic 2
3, Topic 3
4, Topic 4

resourceRelationship
resourceID, resourceTopicID
1, 1
1, 3
2, 1
2, 2
2, 3
2, 4
3, 2
3, 3
3, 4

Now if you joined the tables and outputted the information you'd get the following:

Resource 1
- Topic 1, Topic 3
Resource 2
- Topic 1, Topic 2, Topic 3, Topic 4
Resource 3
- Topic 2, Topic 3, Topic 4
Resource 4
- Topic 1

Now users on the site are going to be able to limit resources based on topics and this is where I'm losing my mind. Here are some examples of what I'd want returned based on different searches.

Searched For: - Searching by topic to return resources.

Topic 1
- Resource 1, Resource 2, Resource 4

Topic 1 and Topic 3
- Resource 1 and Resource 2

Topic 1 and Topic 2 and Topic 3
- Resource 2

Hopefully this is clearer. As you can see when your search gets more targeted the results have to at the least match the topics that were selected but they can include more. If you search for two topics you would never get results that have only 1 topic associated with it but you could get results that have more than the two that were searched for.

When I use "WHERE resourceTopicID IN (1,2,3)" it returns all of the resources. In the tables above that should just return "Resource 2".

Let me know if I can clarify anything more.

THANKS!!