B4J Question jRDC configfile SQL Query select case etc-

[h725]

Hello everybody,
hope you are fine.

i am looking for a solution for the following task:

I have two tables:
customers
ID CUSTOMERNAME

bookings
ID CUSTOMERID AMOUNT

I have customers who have already purchased something. So I can
easily get the amount with:
Select customers.customername, sum(amount) as amount from bookings, customers where customers.id=bookings.customerid AND customers.id = ?

Now I would like to have an overview with the customers who have no entries
in the bookings table (amount = 0.00) and those ones who have purchased something
yet. I tried UNION, but then you 2 entries when somebody has purchased something.

 

[Erel]

Which database server are you using?

MySQL: https://stackoverflow.com/questions...ds-from-one-table-which-dont-exist-in-another

 

[EnriqueGonzalez]

I would do it like this:

    Dim query As String = $"
    SELECT * FROM (
        SELECT c.*,COUNT(b.id) as CountBookings
            FROM customers as c
            LEFT JOIN bookings as b on c.ID = b.CUSTOMERID
        GROUP BY c.ID, c.CUSTOMERNAME
    ) where CountBookings = 0
    "$

 

[h725]

I am using MS SQL Server. I willl check the link and the solution above.
Thank you very much.