Check condition across several rows from a join-CodePudding

I have some entities A which can all have one or more B. How can I query "all entities A which have this and that from B"?

I can query "all entities A which have "this" or "that" from B":

SELECT *
FROM A
INNER JOIN B ON B.A_id = A.id
WHERE f = "this" OR f = "that"
GROUP BY A.ID

But I can’t check that they have "this" and "that", because I can’t check something across different rows from A.

I might use a GROUP_CONCAT, but then how can I effectively check that "blah,thing,foo,bar" has blah and foo without some unmaintainable REGEXP mess?

(I actually love regexes, but not so much in the context of an SQL query, for something that seems like it wouldn’t need a regex).

SELECT *, GROUP_CONCAT(f)
FROM A
INNER JOIN B ON B.A_ID = A.ID
WHERE f = "this" OR f = "that"
GROUP BY A.ID

CodePudding user response：

How can I query "all entities A which have this and that from B"?

For such tasks we usually don't join, but use EXISTS or IN instead. E.g.

select *
from a
where id in (select a_id from b where f = 'this')
  and id in (select a_id from b where f = 'that');

Here is a solution with an aggregation. In this particular case I see no advantage in using this. There are other situations, though, when an aggregation may be the appropriate solution.

select *
from a
where id in 
(
  select a_id 
  from b 
  group by a_id
  having max(f = 'this') = 1
     and max(f = 'that') = 1
);

In MySQL true is 1 and false is 0, so taking the maximum of a boolean expression tells us, whether there is at least one row for which the condition is true.

Your own query works, too, by the way, if you add the appropriate HAVING clause. And there is no regular expression matching needed for that. As your WHEREclause limits f to 'this' and 'that', your GROUP_CONCAT result can never be 'this,something_else;that', but only contain 'this' and 'that'. Well, depending on the table there may be duplicates, like 'this,this,that'. Use an ORDER BY clause and DISTINCT:

SELECT a.*
FROM a
INNER JOIN b ON b.a_id = a.id
WHERE b.f IN ('this', 'that')
GROUP BY a.id
HAVING GROUP_CONCAT(DISTINCT b.f ORDER BY b.f) = 'that,this';