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
