Another IN clause Question
-
I'm having some issues with a Query I'm trying to use to fine similar records in a master table. There are several child tables that will be searched in a similar way. Basically, if there are more than two matches in the sub query I want to display those records. Can anyone point me in the right direction to get this query to work? I understand why I can't use more than one field in my subquery but I can't think of another way to do this.
SELECT *
FROM UC
WHERE uc_key IN (SELECT mo_Key, COUNT(mo_key) as KeyCount
FROM MO
WHERE (mo_MoKey = @Key1 AND mo_Value = @Value1) OR (mo_MoKey = @Key2 AND mo_Value = @Value2) <- there will be a variable number of these that get generated programatically
AND KeyCount > 2)(note, the table names have been mangled to protect the innocent) Thanks, any ideas are appreciated.
-
I'm having some issues with a Query I'm trying to use to fine similar records in a master table. There are several child tables that will be searched in a similar way. Basically, if there are more than two matches in the sub query I want to display those records. Can anyone point me in the right direction to get this query to work? I understand why I can't use more than one field in my subquery but I can't think of another way to do this.
SELECT *
FROM UC
WHERE uc_key IN (SELECT mo_Key, COUNT(mo_key) as KeyCount
FROM MO
WHERE (mo_MoKey = @Key1 AND mo_Value = @Value1) OR (mo_MoKey = @Key2 AND mo_Value = @Value2) <- there will be a variable number of these that get generated programatically
AND KeyCount > 2)(note, the table names have been mangled to protect the innocent) Thanks, any ideas are appreciated.
You probably still want a JOIN; avoid using IN for anything other than literal lists.
-
I'm having some issues with a Query I'm trying to use to fine similar records in a master table. There are several child tables that will be searched in a similar way. Basically, if there are more than two matches in the sub query I want to display those records. Can anyone point me in the right direction to get this query to work? I understand why I can't use more than one field in my subquery but I can't think of another way to do this.
SELECT *
FROM UC
WHERE uc_key IN (SELECT mo_Key, COUNT(mo_key) as KeyCount
FROM MO
WHERE (mo_MoKey = @Key1 AND mo_Value = @Value1) OR (mo_MoKey = @Key2 AND mo_Value = @Value2) <- there will be a variable number of these that get generated programatically
AND KeyCount > 2)(note, the table names have been mangled to protect the innocent) Thanks, any ideas are appreciated.
Hi, Didn't test this so there may be typos but try something like this:
SELECT *
FROM UC
WHERE uc_key IN (SELECT mo_Key
FROM MO
WHERE (mo_MoKey = @Key1 AND mo_Value = @Value1)
OR (mo_MoKey = @Key2 AND mo_Value = @Value2)
GROUP BY mo_Key
HAVING COUNT(*) > 2);Hope this helps, Mika
-
You probably still want a JOIN; avoid using IN for anything other than literal lists.
Using a JOIN will improve speed?
-
Hi, Didn't test this so there may be typos but try something like this:
SELECT *
FROM UC
WHERE uc_key IN (SELECT mo_Key
FROM MO
WHERE (mo_MoKey = @Key1 AND mo_Value = @Value1)
OR (mo_MoKey = @Key2 AND mo_Value = @Value2)
GROUP BY mo_Key
HAVING COUNT(*) > 2);Hope this helps, Mika
Excellent, thank you for your help! One thousand internet dollars are coming your way, just put your ethernet cable into the trash can can to catch it all.
-
Using a JOIN will improve speed?
It has in my experience. And much more flexible.
-
It has in my experience. And much more flexible.
I'll have to experiment in that direction then. This is a nasty complicated app and I'll need all the little speed boosts I can get. Thanks!
-
I'll have to experiment in that direction then. This is a nasty complicated app and I'll need all the little speed boosts I can get. Thanks!
I can imagine. Your first task should be to eliminate the subqueries; they don't benefit from indices. I had to change some of my JOINs the other day because the statements seemed to just sit there, now they zip along.