How To Match A Column Result From Query Result To A Table Column Value And Get More Columns From Same Table?

This is a continuation of my previous question The query is given below, taken from this answer select distinct on (fa_identifier) fa_identifier, fas.case_no from filter_analysis

Solution 1:

You don't need a sub-select for that, just select your output columns carefully:

selectdistinct lm.project_id, case_no
from      filter_analysis_store fas
leftjoin share_analysis_store sas using (fa_identifier)
join      log_metadata lm using (case_no)
where     fas.created_by ='a@a.com'or        sas.shared_to  ='a@a.com';

If you don't want to a case_no appear under multiple project_ids, do a select distinct on (case_no) lm.project_id, case_no instead. You could control under which project_id should case_nos appear with ORDER BY in that case.

Solution 2:

You could use group by without distinct

select fa_identifier, fas.case_no
from   filter_analysis_store fas
left join   share_analysis_store sas using (fa_identifier)
where  fas.created_by = 'a@a.com'or     sas.shared_to  = 'a@a.com'groupby fa_identifier, fas.case_no;