# JOIN Question **URL:** \ **Category:** Toad Data Point & Toad Intelligence Central\ **Created:** [March 26, 2010, 11:03am UTC](https://forums.toadworld.com/t/join-question/6175 "2010-03-26T11:03:50Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![N.B](https://avatars.discourse-cdn.com/v4/letter/n/bc8723/32.png) [@N.B](https://forums.toadworld.com/u/N.B)\ **Post date:** [March 26, 2010, 11:03am UTC](https://forums.toadworld.com/t/join-question/6175/1 "2010-03-26T11:03:50Z")
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. Is there a way to fix this in my join? ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [March 26, 2010, 4:24pm UTC](https://forums.toadworld.com/t/join-question/6175/2 "2010-03-26T16:24:29Z")
Can you post your SQL. It is hard to answer your question without this. Debbie ---
**Author:** ![N.B](https://avatars.discourse-cdn.com/v4/letter/n/bc8723/32.png) [@N.B](https://forums.toadworld.com/u/N.B)\ **Post date:** [March 26, 2010, 5:12pm UTC](https://forums.toadworld.com/t/join-question/6175/3 "2010-03-26T17:12:34Z")
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. ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [March 26, 2010, 8:45pm UTC](https://forums.toadworld.com/t/join-question/6175/4 "2010-03-26T20:45:59Z")
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. ---
**Author:** ![jacquesrk](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/jacquesrk/32/107_2.png) [@jacquesrk](https://forums.toadworld.com/u/jacquesrk)\ **Post date:** [March 29, 2010, 12:37pm UTC](https://forums.toadworld.com/t/join-question/6175/5 "2010-03-29T12:37:21Z")
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) Message was edited by: jacquesrk Message was edited by: jacquesrk