Hot answers tagged

2

Your WHERE clause is preventing any null value of p to carry through to the final result set, so those FULL JOINs will be treated as LEFT JOINs. You don’t want the commas. A comma means CROSS JOIN and doesn’t support an ON clause. What you want is: SELECT p.id, c1.data1, c2.data2 FROM p LEFT JOIN c1 ON p.id = c1.pid LEFT JOIN c2 ON c1.pid = c2.pid ...


1

Thanks for the great data setup. Here's one way to get these hits in SQL. As Aaron mentioned, this can be a CTE without any varibles. WITH cte AS ( SELECT t.AccountId ,t.CompanyId ,t.FacilityId ,CASE WHEN s.AccountId IS NOT NULL THEN 1 ...


1

You use INNER JOIN (in comma-delimited tablenames form) whereas you must use LEFT JOIN. WITH SALES_TODAY AS( /* daily selection logic */ ), sale_id_list AS ( SELECT DISTINCT sale_id FROM SALES_TODAY ) SELECT sale_id_list.sale_id, ... FROM sale_id_list LEFT JOIN SALES_TODAY AS SALE_PROPOSED ON SALE_PROPOSED.sale_id=sale_id_list.sale_id AND ...


Only top voted, non community-wiki answers of a minimum length are eligible