What am I doing wrong in this select query with left join and filters?

Question

With Actian PSQL v13:

I'm attempting to join two tables and filter the result set by a few criteria. One table is customer sales history. The other table associates customers to buying groups. Not all customers are in a buying group, so I'm doing a left join to get all possible sales history results. I do NOT want the results to include anyone in buying group 'SALREP', but I DO want to see customers that are not in any buying groups. Also, the so-called-part 'FREIGHT' is being filtered out of the results.

The problem I'm having is that when I filter out the group 'SALREP' only customers that are in a buying group are being selected; all customers not in a buying group are missing.

I've tested this by commenting out the filter for 'SALREP' and the result set does indeed include customers who are not in a buying group. I also tried using a different filter approach, in case Actian PSQL is picky. For example, I tried different methods of "not equals" such as !=, not() and <>; the problem persists. Also, I've used left join and left outer join with the same problem.

Here is the query:

select T2.GROUP_CUST, T1.DATE_INVOICE, T1.SALESPERSON, T1.CUSTOMER, T1.PRODUCT_LINE, T1.PART, T1.DESCRIPTION, T1.QTY_SHIPPED, T1.EXTENSION 
from ORDER_HIST_LINE T1 
left join BUYING_GROUP T2 on T1.CUSTOMER=T2.CUSTOMER 
where DATE_INVOICE > '2019-06-13' and PART != 'FREIGHT'
and T2.GROUP_CUST != 'SALREP' 
and T1.CUSTOMER in ('ABC', 'DEF', 'GHI')
order by T1.CUSTOMER;

The expected result set should include anything invoiced after 2019-06-13, not include a part called 'FREIGHT' and not have customers in the group called 'SALREP'. However, the actual result set is incomplete. For example:

CUSTOMER | GROUP_CUST | DATE_INVOICE  | PART   | etc.
-----------------------------------------------------
ABC      |  A12       |  2019-06-14   | WIDGET
DEF      |  A12       |  2019-06-14   | GEAR

Basically, all customers who are not in any buying group are left out.

Comment out the portion and T2.GROUP_CUST != 'SALREP' and expected results are found. For example:

CUSTOMER | GROUP_CUST | DATE_INVOICE  | PART   | etc.
-----------------------------------------------------
ABC      |  A12       |  2019-06-14   | WIDGET
DEF      |  A12       |  2019-06-14   | GEAR
GHI      |            |  2019-06-15   | WIDGET

I was thinking about creating the left join to a select query that removes 'SALREP' from the buying groups in the first place, but that doesn't allow the result set to identify and remove anyone from that group. Ex.: left join (select * from BUYING_GROUP where GROUP_CUST != 'SALREP') T2

8/7/19 Further Attempts: I'm finding the same dilemma on MySQL v5.0.12. I can left join tables to yield mismatched results. I can filter the left table on those mismatched results without unexpectedly losing anything. Yet, I cannot filter the right table on those mismatched results without having all mismatched rows disappear.

score 2 · Answer 1 · answered Jun 19 '19 at 18:29

2

You need to put conditions on the second table in the ON clause. The only obvious reference is T2.GROUP_CUST, but it might apply to DATE_INVOICE and PART as well:

from ORDER_HIST_LINE T1 left join
     BUYING_GROUP T2
     on T1.CUSTOMER = T2.CUSTOMER and
        T2.GROUP_CUST <> 'SALREP'
where DATE_INVOICE > '2019-06-13' and
      PART <> 'FREIGHT' and
      T1.CUSTOMER in ('ABC', 'DEF', 'GHI')
order by T1.CUSTOMER;

answered Jun 19 '19 at 18:29

Gordon Linoff

1,242,037
58
646
786

Thank you very much, Gordon. That looks like it did the trick. I was not aware about putting conditions directly on `ON` clauses. I always figured to associate the basic results first and then filtering in the predicate would apply to show final results. – AJK Jun 19 '19 at 18:57
At first this appeared to do the trick, but after having another problem with filtering against T2, it turns out that what's really happening is that the value `SALREP` is being stripped from the `GROUP_CUST` column and the row associated with `SALREP` is still in the result set. – AJK Aug 06 '19 at 21:11

score 0 · Accepted Answer · answered Aug 07 '19 at 19:43

Thank you to https://mode.com/resources/sql-tutorial/sql-joins-where-vs-on/. The article mentions, "filtering in the WHERE clause can also filter null values, so we added an extra line to make sure to include the nulls."

This led me to testing and realizing that when filtering against the right table, it is necessary to explicitly state that NULL values (unmatched rows) for that filtered column are to be included, otherwise those unmatched rows are dropped from the result set. I also found that it's necessary to put the filter pair in parentheses or the result set "explodes". I tested and found this to be necessary only when filtering against the right table; filtering against the left table doesn't have this issue (obviously).

Final answer:

select T2.GROUP_CUST, T1.DATE_INVOICE, T1.SALESPERSON, T1.CUSTOMER, T1.PRODUCT_LINE,             
T1.PART, T1.DESCRIPTION, T1.QTY_SHIPPED, T1.EXTENSION 
from ORDER_HIST_LINE T1 
left join BUYING_GROUP T2 on T1.CUSTOMER=T2.CUSTOMER 
where DATE_INVOICE > '2019-06-13' and PART != 'FREIGHT' 
and (T2.GROUP_CUST != 'SALREP' or T2.GROUP_CUST is null) 
and T1.CUSTOMER in ('ABC', 'DEF', 'GHI') 
order by T1.CUSTOMER;

Notice the line and (T2.GROUP_CUST != 'SALREP' or T2.GROUP_CUST is null).

Maybe it's me, but it seems like explicitly allowing mismatched rows on a `LEFT JOIN` shouldn't be necessary, no matter which table has filters applied. — AJK, Aug 07 '19 at 19:44

What am I doing wrong in this select query with left join and filters?

2 Answers2