random_thoughts_March62018.sql

-- idea #1 : Map jobs to a company using a correlated subquery
-- 
select j.id_1 as job_id, 
	COALESCE((
		 SELECT c.id_1 FROM company c 
		  WHERE c.id_1 = j.id_1
	), 0) as matching_company_1,
	
	COALESCE((
		 SELECT c.id_2 FROM company c 
		  WHERE c.id_2 = j.id_1
		  LIMIT 1 -- not sure about the data, but erroring out in some cases due to subq return mult rows
	), 0) as matching_company_2

FROM jobs j 

-- Try removing the LIMIT  above, see error: Avoid the LIMIT 1 in the 2nd subquery, again, not sure what the data is all about
-- Hard to reason about the right procedure :)

SELECT t.*, array_agg(c3.id_2)
FROM (
	select j.id_1 as job_id, 
		COALESCE((
			 SELECT c.id_1 FROM company c 
			  WHERE c.id_1 = j.id_1
		), 0) as matching_company_1

	FROM jobs j 
)t LEFT JOIN company c3 ON c3.id_2 = t.job_id
GROUP BY 1, 2