DECLARE @t1 TABLE(id INT); INSERT INTO @t1(id) VALUES(1); INSERT INTO @t1(id) VALUES(2); INSERT INTO @t1(id) VALUES(3); DECLARE @t2 TABLE (itemid INT, id INT ); INSERT INTO @t2 (itemid, id ) VALUES (1, 1 ); INSERT INTO @t2 (itemid, id ) VALUES (1, 2 ); INSERT INTO @t2 (itemid, id ) VALUES (1, 3 ); INSERT INTO @t2 (itemid, id ) VALUES (2, 1 ); INSERT INTO @t2 (itemid, id ) VALUES (2, 2 ); -- this gives me the values in @t1 as 'OR' (1 or 2 or 3) found in @t2 SELECT DISTINCT A.itemid FROM @t2 A INNER JOIN @t1 B ON A.id = B.id; --itemid --1 --2 -- but I need the 'AND' results; itemids from @t2 where ids are (1 and 2 and 3) from: select * from @t1 -- effectively like -- select distinct itemid from @t2 -- where id=1 and id=2 and id=3 -- so expected result is: --itemid --1 -- because only itemid=1 has a rows with all entries in @t2
I have come up with this solution, could it be improved?
SELECT itemid FROM @t2 B INNER JOIN @t1 A ON B.id =A.id WHERE B.id IN ( SELECT id FROM @t1 ) GROUP BY B.itemid HAVING COUNT(*) = ( SELECT COUNT(*) FROM @t1 );
Probably several ways to do this, not sure what the most efficient is. Here's my shot:
I don't think you want a JOIN, you don't actually want to use anything from table B - because you need 3 separate rows from Table B, but if you do need the rows then JOIN it three times (to prove that all 3 B.itemid do exist)
This assumes that there are no duplicate values for [itemid] for a given [id] value in Table B
; WITH CTE AS ( SELECT id FROM @t1 AS B WHERE itemid IN (1, 2, 3) GROUP BY id HAVING COUNT(*) = 3 ) SELECT DISTINCT A.itemid FROM @t2 AS A JOIN CTE AS B ON B.id = A.id
I think the low volume of replies in this case may reflect the ambiguity. Does the @t1 and @t2 reflect your actual data structure? Can an id exist in @t2 but not in @t1? Can you have duplicates in @t1? Aside from plugging in known values ie "HAVING count(*) = 3", which would have to be maintained manually so is not advised for any code that sits in place, would the very simplest option cover your data content?
FROM @t2 B
group by b.itemid
HAVING COUNT() = (SELECT COUNT() FROM @t1);
Try a few variations of solutions, but set statistics on, or look at execution plans to get a feel for what's most efficient for you. The above is incredibly simple, but assumes values in @t1 are unique. It also assumes that no id in @t2 will be missing from @t1.
Answer to your questions:
Yes (for the sake of this question anyway),
yes and no
(apologies for the initial ambiguity)
it is for a tagging engine.
There are many rows 100s of 1000s of items in @t2
@t1 is the list of tags selected to use to search items with, so only a few (<20ish) but maybe there are chosen tags not found in any item in @t2 and certainly many items in @t2 with tags not chosen for the search in @t1.
I need two result lists one OR'd and one AND'd.
SELECT DISTINCT A.itemid, B.B_Count FROM @t2 AS A JOIN ( SELECT B.id, COUNT(*) AS B_Count FROM @t1 AS B WHERE B.itemid IN (1, 2, 3) GROUP BY B.id ) AS B ON B.id = A.id
will give you the number of matches. B_Count=3 indicates that all items match, a lower number indicates the number of (tag) matches
Would that sort of approach help?