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

Question

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!

When you wrote `JOIN Profile p ON c.id_customer = p.id_profile`, did you mean `JOIN Profile p ON c.id_customer = p.id_customer`? — jason44107, Sep 11 '21 at 03:47
r.text needs to be in the GROUP BY or be part of an aggregate function in the SELECT — NickW, Sep 11 '21 at 08:29
@jason44107 i meant that the value of id_profile correlates to the value of id_customer (my real life example has these quirks unfortunately) Editing the example to make it more readable, thanks! — Juan M, Sep 11 '21 at 09:10
Your query doesn't work in either TSQL or Sybase, because the `group by` clause and the `select` columns are inconsistent. In addition, you should tag only the database you are really using. And what do you want if there are multiple reviews? — Gordon Linoff, Sep 11 '21 at 12:18
assuming you're running on a `Sybase` database ... which Sybase RDBMS product (ASE? IQ? SQLAnywhere? Advantage?) and version? — markp-fuso, Sep 11 '21 at 14:17
@GordonLinoff thanks for the comment; I guess i missed that one when trying to think of an example. I'm not concerned with multiple reviews for this example, let's say they're unique per order :) — Juan M, Sep 12 '21 at 20:04

forpas · Accepted Answer · 2021-09-11T10:27:02.860

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.

score 0 · Answer 2 · answered Sep 11 '21 at 12:21

If you just want all the review information with the maximum order date, then you can summarize the orders table and join in the rest:

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

This will return multiple rows if the id_customer has multiple reviews. Your question is silent on how to deal with that.

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

2 Answers2