I’ve used Query Build to design and build a 3 table join, which for the most part works fine, but I’m getting results I shouldn’t, and I’ve figured out why, but now how to fix it.
I’m JOIN I’m having issues with involves a table called Customers, which contains all the relevant customer information, joined to a table called Customer Products, which contains 1 row for each product that a customer has - so I believe this is a one-to-many join.
The problem is that I’m trying to filter out customers who have certain products in the Customer Products table, but I’m still getting them on the result set - because it’s apparently only filtering that row, not the entire customer.
Sure; this is the SQL generated by Query Builder when I export it to SQL Editor:
SELECT ID_C.AC,
ID_H.C,
ID_C.H,
ID_C.CT,
ID_C.STAT
FROM (GGS.ID_H ID_H
INNER JOIN GGS.ID_C ID_C
ON (ID_H.AC = ID_C.AC)
AND (ID_H.H = ID_C.H)
AND (ID_H.CT = ID_C.CT))
INNER JOIN GGS.ID_CT_R ID_CT_R
ON (ID_CT_R.AC = ID_C.AC)
AND (ID_CT_R.H = ID_C.H)
AND (ID_CT_R.CT = ID_C.CT)
WHERE (ID_C.AC = 5)
AND (ID_H.C IN ('A4570', 'A5900', 'A6000'))
AND (ID_C.STAT IN ('1', '2', '3', '4', '5'))
AND (ID_CT_R.SERV NOT IN ('T', 'L', 'X'))
The relationship between ID_C and ID_CT_R is a one to many, in that there can, and are, multiple rows in ID_CT_R for each row in ID_C. The problem I'm running into is that the select isn't discarding the entire record if it finds one row in ID_CT_R that matches on the ID_CT_R.SERV filter.
A match on the filter of ID_CT_R.SERV would be any value that is not ‘T’, ‘L’, or ‘X’. So are you saying you are getting a ‘T’, ‘L’ or ‘X’ in your result set? I set up some sample tables with data and do not get this. Take a screen shot of your result set because I am not understanding the issue.
I might be wrong, but I take the original question to mean “I want my query to not show any customers that have a T, L or X in the customer_products table”. If so the query should be something like
select * from customers
WHERE NOT EXISTS
(select null from customer_products
where customers.customer_id = customer_products.customer_id
and customer_products.product_type in (‘T’, ‘L’, ‘X’)) ;
(you would need to modify this to have the proper table/column names, and to use ANSI join syntax)