T-SQL: How do I get the rows from one table whose values completely match up with values in another table?

declare @a table
(
    pkid int,
    value int
)

declare @b table
(
    otherID int,
    value int
)


insert into @a values (1, 1000)
insert into @a values (1, 1001)
insert into @a values (2, 1000)
insert into @a values (2, 1001)
insert into @a values (2, 1002)

insert into @b values (-1, 1000)
insert into @b values (-1, 1001)
insert into @b values (-1, 1002)

insert into @b values (-2, 1000)

SELECT a.pkId,b.otherId FROM
	(SELECT a.pkId,CHECKSUM_AGG(DISTINCT a.value) as 'ValueHash' FROM @a a GROUP BY a.pkId) a
	INNER JOIN (SELECT b.otherId,CHECKSUM_AGG(DISTINCT b.value) as 'ValueHash' FROM @b b GROUP BY b.otherId) b
ON a.ValueHash = b.ValueHash

SELECT  *
FROM    (
        SELECT  ab.pkid, ab.otherID,
                (
                SELECT  TOP 1 COALESCE(ai.value, bi.value)
                FROM    (
                        SELECT  *
                        FROM    @a aii
                        WHERE   aii.pkid = ab.pkid
                        ) ai
                FULL OUTER JOIN
                        (
                        SELECT  *
                        FROM    @b bii
                        WHERE   bii.otherID = ab.otherID
                        ) bi
                ON      ai.value = bi.value
                WHERE   ai.pkid IS NULL OR bi.otherID IS NULL
                ) unmatch
        FROM
                (
                SELECT  DISTINCT pkid, otherid
                FROM    @a a , @b b
                ) ab
        ) q
WHERE   unmatch IS NOT NULL

select A.pkid, B.otherId
    from @a A, @b B
    where A.value = B.value
    group by A.pkid, B.otherId
    having count(B.value) = (
    	select count(*) from @b BB where B.otherId = BB.otherId)

SELECT
    SQ1.pkid
FROM
    (
    	SELECT
    		a.pkid, COUNT(*) AS cnt
    	FROM
    		@a AS a
    	GROUP BY
    		a.pkid
    ) SQ1
INNER JOIN
    (
    	SELECT
    		a1.pkid, b1.otherID, COUNT(*) AS cnt
    	FROM
    		@a AS a1
    	INNER JOIN @b AS b1 ON b1.value = a1.value
    	GROUP BY
    		a1.pkid, b1.otherID
    ) SQ2 ON
    	SQ2.pkid = SQ1.pkid AND
    	SQ2.cnt = SQ1.cnt
INNER JOIN
    (
    	SELECT
    		b2.otherID, COUNT(*) AS cnt
    	FROM
    		@b AS b2
    	GROUP BY
    		b2.otherID
    ) SQ3 ON
    	SQ3.otherID = SQ2.otherID AND
    	SQ3.cnt = SQ1.cnt

declare @a table
(
    pkid int,
    value int
)

declare @b table
(
    otherID int,
    value int
)


insert into @a values (1, 1000)
insert into @a values (1, 1001)

insert into @a values (2, 1000)
insert into @a values (2, 1001)
insert into @a values (2, 1002)

insert into @a values (3, 1000)
insert into @a values (3, 1001)
insert into @a values (3, 1001)

insert into @a values (4, 1000)
insert into @a values (4, 1000)
insert into @a values (4, 1001)


insert into @b values (-1, 1000)
insert into @b values (-1, 1001)
insert into @b values (-1, 1002)

insert into @b values (-2, 1001)
insert into @b values (-2, 1002)

insert into @b values (-3, 1000)
insert into @b values (-3, 1001)
insert into @b values (-3, 1001)



SELECT Matches.pkid, Matches.otherId
FROM
(
    SELECT a.pkid, b.otherId, n = COUNT(*)
    FROM @a a
    INNER JOIN @b b
    	ON a.Value = b.Value
    GROUP BY a.pkid, b.otherId
) AS Matches

INNER JOIN
(
    SELECT
    	pkid,
    	n = COUNT(DISTINCT value)
    FROM @a
    GROUP BY pkid
) AS ACount
ON Matches.pkid = ACount.pkid

INNER JOIN
(
    SELECT
    	otherId,
    	n = COUNT(DISTINCT value)
    FROM @b
    GROUP BY otherId
) AS BCount
    ON Matches.otherId = BCount.otherId

WHERE Matches.n = ACount.n AND Matches.n = BCount.n

declare @a table
(
    pkid int,
    value int
)

declare @b table
(
    otherID int,
    value int
)


insert into @a values (1, 1000)
insert into @a values (1, 1001)

insert into @a values (2, 1000)
insert into @a values (2, 1001)
insert into @a values (2, 1002)

insert into @a values (3, 1000)
insert into @a values (3, 1001)
insert into @a values (3, 1001)

insert into @a values (4, 1000)
insert into @a values (4, 1000)
insert into @a values (4, 1001)


insert into @b values (-1, 1000)
insert into @b values (-1, 1001)
insert into @b values (-1, 1002)

insert into @b values (-2, 1001)
insert into @b values (-2, 1002)

insert into @b values (-3, 1000)
insert into @b values (-3, 1001)
insert into @b values (-3, 1001)



SELECT Matches.pkid, Matches.otherId
FROM
(
    SELECT a.pkid, b.otherId, n = COUNT(*)
    FROM @a a
    INNER JOIN @b b
    	ON a.Value = b.Value
    GROUP BY a.pkid, b.otherId
) AS Matches

INNER JOIN
(
    SELECT
    	pkid,
    	n = COUNT(DISTINCT value)
    FROM @a
    GROUP BY pkid
) AS ACount
ON Matches.pkid = ACount.pkid

INNER JOIN
(
    SELECT
    	otherId,
    	n = COUNT(DISTINCT value)
    FROM @b
    GROUP BY otherId
) AS BCount
    ON Matches.otherId = BCount.otherId

WHERE Matches.n = ACount.n AND Matches.n = BCount.n

select
    matches.pkid
    ,matches.otherID
from
(
    select
    	a.pkid
    	,b.otherID
    	,count(1) as cnt
    from @a a
    inner join @b b
    	on b.value = a.value
    group by
    	a.pkid
    	,b.otherID
) as matches
inner join
(
    select
    	otherID
    	,count(1) as cnt
    from @b
    group by otherID
) as b_counts
on b_counts.otherID = matches.otherID
where matches.cnt = b_counts.cnt

select a.*
from @a a
inner join @b b on a.value = b.value

select
	a.pkid,
	a.value
from
	@a a
where
	a.pkid in
	(
	select
		pkid
	from
		(
		select
			c.pkid,
			c.otherid,
			count(*) matching_count
		from
			(
			select
				a.pkid,
				a.value,
				b.otherid
			from
				@a a inner join @b b
				on a.value = b.value
			) c
		group by
			c.pkid,
			c.otherid
		) d
		inner join
		(
		select
			b.otherid,
			count(*) b_record_count
		from
			@b b
		group by
			b.otherid
		) e
		on d.otherid = e.otherid
		and d.matching_count = e.b_record_count
		inner join
		(
		select
			a.pkid match_pkid,
			count(*) a_record_count
		from
			@a a
		group by
			a.pkid
		) f
		on d.pkid = f.match_pkid
		and d.matching_count = f.a_record_count
	)

from
 (select cnt.cnt, cnt.cntid1, cnt.cntid2 from
    (select acnt.cnt as cnt, acnt.cntid as cntid1, bcnt.cntid as cntid2 from
          (select count(pkid) as cnt, pkid as cntid from @a group by pkid)
           as acnt
                full join
               (select count(otherID) as cnt, otherID as cntid from @b group by otherID)
                as bcnt
                   on  acnt.cnt = bcnt.cnt)
     as cnt
     where cntid1 is not null and cntid2 is not null)
   as cntok
inner join
(select count(1) as cnt, cnta.cntid1 as cntid1, cnta.cntid2 as cntid2
from
    (select cnt, cntid1, cntid2, a.value as value1
     from
         (select cnt.cnt, cnt.cntid1, cnt.cntid2 from
            (select acnt.cnt as cnt, acnt.cntid as cntid1, bcnt.cntid as cntid2 from
                  (select count(pkid) as cnt, pkid as cntid from @a group by pkid)
                   as acnt
                        full join
                       (select count(otherID) as cnt, otherID as cntid from @b group by otherID)
                        as bcnt
                           on  acnt.cnt = bcnt.cnt)
             as cnt
             where cntid1 is not null and cntid2 is not null)
         as cntok
             inner join @a as a on a.pkid = cntok.cntid1)
      as cnta
         inner join

             (select cnt, cntid1, cntid2, b.value as value2
             from
             (select cnt.cnt, cnt.cntid1, cnt.cntid2 from
                    (select acnt.cnt as cnt, acnt.cntid as cntid1, bcnt.cntid as cntid2 from
                          (select count(pkid) as cnt, pkid as cntid from @a group by pkid)
                           as acnt
                                full join
                               (select count(otherID) as cnt, otherID as cntid from @b group by otherID)
                                as bcnt
                                   on  acnt.cnt = bcnt.cnt)
                     as cnt
                     where cntid1 is not null and cntid2 is not null)
                 as cntok
                     inner join @b as b on b.otherid = cntok.cntid2)
               as cntb
               on cnta.cntid1 = cntb.cntid1 and cnta.cntid2 = cntb.cntid2 and cnta.value1 = cntb.value2
      group by cnta.cntid1, cnta.cntid2)
   as cntequals
   on cntok.cnt = cntequals.cnt and cntok.cntid1 = cntequals.cntid1 and cntok.cntid2 = cntequals.cntid2

Select * -- all columns but only from #a
from #a
inner join #b
on #a.value = #b.value -- only return matching rows
where #a.pkid  = 2