How can I achieve this query with the Designer?

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!

Comments

  • 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.


    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • 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


  • 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)





    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • 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!!
Sign In or Register to comment.