How can I achieve this query with the Designer?

santycg

Hi!

I'm using RBuilder 20 and Delphi 10.4.

I need to show all records from Table A which are NOT in Table B.
For example: show all customers I haven't sold anything.

The only way I found is to use a custom manually-edited sql query using subqueries, but I can't finf a way to achieve this using the Designer options.

Is it possible?

Thanks!

nicocizik

Hi Luis,

This should be possible to do without the use of manual SQL. What have you tried so far? What does your manual SQL look like?

Generally you will want to join the two tables on a shared field, then create a search condition where the field value is blank (NULL) for the second table.

santycg

Hi, Nico.

Thanks for your reply.

I tried joining both tables, also with 'links' and even with report parameters with no success.

This is my manual sql:

SELECT customers.ID, customers.NAME
FROM customers
WHERE customers.ID not in (select CustomerID from sales)
ORDER BY customers.CODIGO

Sorry, I tried what you suggested with no luck. I don't know if I'm doing it correctly.

I create a new dataview, select both tables (first 'customers', second 'sales', and viceversa) with an inner join using the customers.ID and sales.CustomerID (i also tried with left and right joins with no success).
Then added a search condition where 'sales.CustomerID is Null', but I get no records.

I also tried creating two dataviews (one for customers and another for sales), linking them, etc. but I never get what I need

I'm using DBISAM database

nardmoseley

Hi Luis,

To implement that SQL in the query designer

- Table page - choose customers table
- Fields page - choose ID, Name
- Search page - choose ID, operator: Not In List, Value: (Select CustomID from Sales)

santycg

OMG, I didn't know you can enter a 'select' expression in the Value box!!

However, it is not possible to 'search' the sales table as it is not available in the 'tables' page, so auto-search will not work, isnt't it?

Thanks btw! This will help me a lot with similar reports!!