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.
>Solution :
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;