[SOLVED] Combining Left Outer and Inner Joins + Aggregate Function

Combining Left Outer and Inner Joins + Aggregate Function - Empty result set issue

This may be quite simple but I'm not seeing it at the moment. Trying to combine Left outer joins and inner joins in order to fetch whatever info is available from a given set of tables, all related to a customer_id

Example may not be perfect by design (I made it up based on my actual query), but should suffice in order to illustrate my issue, which is an empty result set even though there are rows in some of these tables.

Sample Tables:

Profile:

id_profile   nm_profile 
-----------  ----------  
1234         User profile

Orders:

id_order id_customer order_date    order_type
-------  ----------  ---------     ----------     
10308    1234        2017-09-18    Online
10309    1234        2018-09-18    Online

Reviews:

   id_review  id_profile   id_order   text    score   
   ---------  ----------   --------   -----   ------
   (no rows for this id_profile)

Query:

SELECT c.id_customer, MIN(o.order_date) order_date, r.text review_text
FROM Customer c
JOIN Profile p ON c.id_customer = p.id_profile 
LEFT OUTER JOIN Orders o ON o.id_customer = c.id_customer AND o.order_type = 'Online'
LEFT OUTER JOIN Reviews r ON r.id_reviewer = p.id_profile AND r.score = 5
WHERE c.id_customer = 1234
GROUP BY c.id_customer

Assuming these columns match and I'm able to run the above query, I'm trying to achieve the following:

id_customer    order_date   review_text
-----------    ----------   -----------
1234           2017-09-18   <NULL>

This is part of a much larger query; trying to break it down to its most basic expression to understand what I may be doing wrong. Have tried to avoid WHERE clauses in the joins and also attempted LEFT OUTER JOIN (SELECT ....) , but no luck.

Thanks in advance!

Solution

You should join Reviews after the aggregation is done:

SELECT t.id_customer, t.order_date, r.text review_text
FROM ( 
  SELECT c.id_customer, MIN(o.order_date) order_date
  FROM Customer c
  INNER JOIN Profile p ON c.id_customer = p.id_profile 
  LEFT JOIN Orders o ON o.id_customer = c.id_customer AND o.order_type = 'Online'
  WHERE c.id_customer = 1234
  GROUP BY c.id_customer
) t
LEFT JOIN Reviews r ON r.id_reviewer = t.id_customer AND r.score = 5;

See the demo.