MySQL how to compare list of strings with subquery-CodePudding

I am trying to write a query that can match a list of strings on a subquery. Example

TableA

id	value
1	somevalue1
2	somevalue2

TableB

id	value	tableA_id
1	test1	1
2	test2	1
3	test1	2

I need a query that returns all the entries from TableA who have an entry in TableB for a list of strings.

For:

select * from TableA ta where ('test1', 'test2') = (select tb.value from TableB tb where tb.tableA_id = ta.id);

Expected result would be

id	value
1	somevalue1

because this is the only entry in TableA that has entries for both those string values in TableB.

I tried to look on the internet on how to match a list of string in MySQL but didn't found something that I can use, my sql skills are at beginner level.

Thanks in advance.

CodePudding user response：

Actually you may not even need to involve TableA in this query. I suggest the following canonical aggregation approach on TableB:

SELECT tableA_id
FROM TableB
WHERE value IN ('test1', 'test2')
GROUP BY tableA_id
HAVING COUNT(DISTINCT value) = 2;

CodePudding user response：

SELECT DISTINCT ta.id FROM 
TableA ta, TableB tb
WHERE (ta.id = tb.id)
AND tb.value in ('test1', 'test2')