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

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;