SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TableA
(cid nvarchar(10),condition nvarchar(20));
INSERT INTO TableA SELECT cid=N'B證照',condition=N'A證照';
INSERT INTO TableA SELECT cid=N'D證照',condition=N'C證照';
INSERT INTO TableA SELECT cid=N'E證照',condition=N'A證照';
INSERT INTO TableA SELECT cid=N'E證照',condition=N'B證照';
INSERT INTO TableA SELECT cid=N'G證照',condition=N'F證照';
5 rows affected

SELECT *
FROM TableA


cid
condition


B證照
A證照

D證照
C證照

E證照
A證照

E證照
B證照

G證照
F證照


CREATE TABLE TableB
(cid nvarchar(10),ID nvarchar(20));
INSERT INTO Tableb SELECT cid=N'A證照',condition='2';
INSERT INTO Tableb SELECT cid=N'A證照',condition='4';
INSERT INTO Tableb SELECT cid=N'A證照',condition='6';
INSERT INTO Tableb SELECT cid=N'A證照',condition='7';
INSERT INTO Tableb SELECT cid=N'B證照',condition='7';
INSERT INTO Tableb SELECT cid=N'C證照',condition='1';
INSERT INTO Tableb SELECT cid=N'C證照',condition='8';
INSERT INTO Tableb SELECT cid=N'C證照',condition='9';
INSERT INTO Tableb SELECT cid=N'E證照',condition='3';
INSERT INTO Tableb SELECT cid=N'E證照',condition='4';
INSERT INTO Tableb SELECT cid=N'E證照',condition='5';
INSERT INTO Tableb SELECT cid=N'E證照',condition='9';
INSERT INTO Tableb SELECT cid=N'F證照',condition='1';
INSERT INTO Tableb SELECT cid=N'F證照',condition='2';
INSERT INTO Tableb SELECT cid=N'F證照',condition='8';
15 rows affected

SELECT *
FROM TableB


cid
ID


A證照
2

A證照
4

A證照
6

A證照
7

B證照
7

C證照
1

C證照
8

C證照
9

E證照
3

E證照
4

E證照
5

E證照
9

F證照
1

F證照
2

F證照
8


SELECT TableTemp.cid,TableTemp.ID
FROM (
SELECT TableA.cid,TableA.condition,TableB.ID,count(1) as count_x
FROM TableA
INNER
JOIN TableB ON TableA.condition=TableB.cid
WHERE 1=1
AND TableA.cid=N'B證照'
GROUP BY TableA.cid,TableA.condition,TableB.ID
) AS TableTemp
WHERE 1=1
GROUP BY TableTemp.cid,TableTemp.ID
HAVING sum(TableTemp.count_x) = (
SELECT COUNT(1) count_y
FROM TableA
WHERE 1=1
AND TableA.cid=N'B證照'
GROUP BY TableA.cid
)
ORDER BY TableTemp.ID


cid
ID


B證照
2

B證照
4

B證照
6

B證照
7


SELECT TableTemp.cid,TableTemp.ID
FROM (
SELECT TableA.cid,TableA.condition,TableB.ID,count(1) as count_x
FROM TableA
INNER
JOIN TableB ON TableA.condition=TableB.cid
WHERE 1=1
AND TableA.cid=N'E證照'
GROUP BY TableA.cid,TableA.condition,TableB.ID
) AS TableTemp
WHERE 1=1
GROUP BY TableTemp.cid,TableTemp.ID
HAVING sum(TableTemp.count_x) = (
SELECT COUNT(1) count_y
FROM TableA
WHERE 1=1
AND TableA.cid=N'E證照'
GROUP BY TableA.cid
)
ORDER BY TableTemp.ID


cid
ID


E證照
7
cid	condition
B證照	A證照
D證照	C證照
E證照	A證照
E證照	B證照
G證照	F證照