How to include empty rows in a result set?
Posted on 2014-11-13
I have the following SQL SELECT:
A.CUSTOMERSTYLE_ID, B.STYLE_NAME, A.COLOR_ID, C.COLOR_NAME, A.SIZES,
A.PRODORDER_NO, A.FULFILLMENTORDER, SUM( D.QUANTITY ), SUM( D.QUANTITYALLOW )
FROM CUSTOMER_POSTYSCOLS A, CUSTOMER_STYLES B, CUSTOMER_COLORS C, CUSTOMER_POSIZES D
A.COMPANY_ID = B.COMPANY_ID AND
A.CUSTOMER_ID = B.CUSTOMER_ID AND
A.CUSTOMERSTYLE_ID = B.CUSTOMERSTYLE_ID AND
A.COMPANY_ID = C.COMPANY_ID AND
A.CUSTOMER_ID = C.CUSTOMER_ID AND
A.COLOR_ID = C.COLOR_ID AND
A.COMPANY_ID = D.COMPANY_ID AND
A.CUSTOMER_ID = D.CUSTOMER_ID AND
A.CUSTOMERSTYLE_ID = D.CUSTOMERSTYLE_ID AND
A.COLOR_ID = D.COLOR_ID AND
A.COMPANY_ID = 0 AND
A.CUSTOMER_ID = 7 AND
A.CUSTOMERPO_ID = 1
GROUP BY A.CUSTOMERSTYLE_ID, B.STYLE_NAME, A.COLOR_ID, C.COLOR_NAME, A.SIZES, A.PRODORDER_NO, A.FULFILLMENTORDER, A.ROWNO
ORDER BY A.ROWNO
This SQL SELECT returns 2 rows, instead of 3. This is because CUSTOMER_STYLES has 3 lines, but CUSTOMER_POSIZES references 2 --in other words, there is a record in CUSTOMER_STYLES that doesn't have child records in CUSTOMER_POSIZES. I need to write this SQL SELECT, in a way, that it returns me 3 rows.
I will wait for your feedback.
Best regards, and thank you in advance.