SELECT using JOIN where result counts per join are less than n? I am trying to query tableA, joining or sub-querying on tableB, where the count of matching rows from tableB are less than 4.
Example:
tableA
+-------------------+
|columnA|columnB    |
+-------------------+
|item1  |focaccia   |
|item2  |spinach    |
|item3  |goat cheese|
|item4  |beets      |
+-------------------+

tableB
+-------------------+
|columnA|columnB    |
+-------------------+
|blah1  |focaccia   |
|blah2  |focaccia   |
|blah3  |focaccia   |
|blah4  |spinach    |
|blah5  |goat cheese|
|blah6  |goat cheese|
|blah7  |goat cheese|
|blah8  |goat cheese|
|blah9  |goat cheese|
|blah10 |goat cheese|
|blah11 |beets      |
|blah12 |beets      |
|blah13 |beets      |
|blah14 |beets      |
|blah15 |beets      |
|blah16 |beets      |
|blah17 |beets      |
+-------------------+

The desired result from my SELECT from tableA here would be:
+-------------------+
|columnA|columnB    |
+-------------------+
|item1  |focaccia   |
|item2  |spinach    |
+-------------------+

... since the count of other matching items is greater than 4 each.
Is this possible?
you can use a subquery to fin all columB that have a lower countg tha 4


CREATE TABLE TableA
    (&quot;columnA&quot; varchar(5), &quot;columnB&quot; varchar(11))
;
    
INSERT INTO TableA
    (&quot;columnA&quot;, &quot;columnB&quot;)
VALUES
    ('item1', 'focaccia'),
    ('item2', 'spinach'),
    ('item3', 'goat cheese'),
    ('item4', 'beets')
;


CREATE TABLE TableB
    (&quot;columnA&quot; varchar(6), &quot;columnB&quot; varchar(11))
;
    
INSERT INTO TableB
    (&quot;columnA&quot;, &quot;columnB&quot;)
VALUES
    ('blah1', 'focaccia'),
    ('blah2', 'focaccia'),
    ('blah3', 'focaccia'),
    ('blah4', 'spinach'),
    ('blah5', 'goat cheese'),
    ('blah6', 'goat cheese'),
    ('blah7', 'goat cheese'),
    ('blah8', 'goat cheese'),
    ('blah9', 'goat cheese'),
    ('blah10', 'goat cheese'),
    ('blah11', 'beets'),
    ('blah12', 'beets'),
    ('blah13', 'beets'),
    ('blah14', 'beets'),
    ('blah15', 'beets'),
    ('blah16', 'beets'),
    ('blah17', 'beets')




SELECT a.* FROM TableA AS a INNER JOIN (SELECT &quot;columnB&quot; FROM Tableb GROUP BY &quot;columnB&quot; HAVING COUNT(*) &lt; 4) AS b
ON a.&quot;columnB&quot; = b.&quot;columnB&quot;


columnA | columnB 
:------ | :-------
item1   | focaccia
item2   | spinach 


db&lt;&gt;fiddle here