SQL find rows in different tables using the same ID-CodePudding

I have tableA containing a column called user_id AND another column called flag_count I need to filter all the rows in tableA with a flag_count=12 and then use the user_id in those rows to query tableB and return all the rows using the same user_id AND said rows also contain a column value of registered=true

This is what I have so far:

select * from public.tableA where user_id in (
    select user_id from public.tableB
    where flag_count = 12
    group by user_id having count(*) = 1
)
and registered='true'

This looks straight forward enough but I cannot find a simple solution. Any help with this would be appreciated.

CodePudding user response：

You can have it work more efficiently by using a JOIN operation and adding a index on "public.tableA.user_id".

WITH cte AS (
    SELECT user_id 
    FROM public.tableB
    WHERE flag_count = 12
    GROUP BY user_id 
    HAVING count(*) = 1
)
SELECT * 
FROM       public.tableA a
INNER JOIN cte
        ON a.user_id = cte.user_id AND a.registered = 'true'

Subquery may be further simplified, though it depends on the kind of data you have. Having sample data could help for that.