SELECT using JOIN where result counts per join are less than n?

Question

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?

Answer 1

you can use a subquery to fin all columB that have a lower countg tha 4

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


CREATE TABLE TableB
    ("columnA" varchar(6), "columnB" varchar(11))
;
    
INSERT INTO TableB
    ("columnA", "columnB")
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 "columnB" FROM Tableb GROUP BY "columnB" HAVING COUNT(*) < 4) AS b
ON a."columnB" = b."columnB"

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

db<>fiddle here

Answer 2

This is MSSQL syntax, but I believe the equivalent in postgresql is LEFT JOIN LATERAL. Hope it helps. Didn't realize it was postgresql until I already wrote it all out :-p

DECLARE @TableA TABLE
(
    columnA  VARCHAR(50)
    ,columnB VARCHAR(50)
);

DECLARE @TableB TABLE
(
    columnA  VARCHAR(50)
    ,columnB VARCHAR(50)
);

INSERT INTO @TableA
(
    columnA
    ,columnB
)
SELECT 'item1' , 'focaccia'
UNION ALL
SELECT 'item2' , 'spinach'
UNION ALL
SELECT 'item3' , 'goat cheese'
UNION ALL
SELECT 'item4' , 'beets';

INSERT INTO @TableB
(
    columnA
    ,columnB
)
SELECT 'blah1','focaccia'
UNION ALL SELECT 'blah2','focaccia'
UNION ALL SELECT 'blah3','focaccia'
UNION ALL SELECT 'blah4','spinach'
UNION ALL SELECT 'blah5','goat cheese'
UNION ALL SELECT 'blah6','goat cheese'
UNION ALL SELECT 'blah7','goat cheese'
UNION ALL SELECT 'blah8','goat cheese'
UNION ALL SELECT 'blah9','goat cheese'
UNION ALL SELECT 'blah10','goat cheese'
UNION ALL SELECT 'blah11','beets'
UNION ALL SELECT 'blah12','beets'
UNION ALL SELECT 'blah13','beets'
UNION ALL SELECT 'blah14','beets'
UNION ALL SELECT 'blah15','beets'
UNION ALL SELECT 'blah16','beets'
UNION ALL SELECT 'blah17','beets'

SELECT  ta.columnA
        ,ta.columnB
        ,r.HitCount
FROM    @TableA AS ta
        CROSS APPLY (
                        SELECT  COUNT(1) AS HitCount
                        FROM    @TableB AS tb
                        WHERE   ta.columnB = tb.columnB
                    ) AS r
WHERE   r.HitCount < 4;

Answer 3

I may have misread the question, but a straight forward answer is

Select a."columnA", a."columnB"
From TableA a
Join TableB b
    Using ("columnB")
Group by a."columnA", a."columnB"
Having count(1)<4