Select AND logic rather than OR in table join

,
1 
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
2

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
);
3

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
4

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?

SELECT b.itemid
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.

5

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.

6 
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?

7

@Kristen
yes it is a good idea because I can order by B_Count desc and so display a measure of relevance, thanks.