unequally yoked: custom sql statement in a list control

[bob9145]

Trying to join and filter two tables

customer
C_Id
First
Last

payments
P_Id
Paid (logical)
C_Id

All customer records do not have a child record in the payment table.
Want to retrieve all customer records except those that do have a child record in the payment table
where pay = True
So the result set of records will exclude any customers that were already paid.
using mysql

Tried the different joins and filters through the builder, but I don't think it will accommodate what I'm trying to do.
Thanks,
Bob

[lgrupido]

Re: unequally yoked: custom sql statement in a list control

what database are you using?

one possible select statement would be something like:

select * from customer where (select count(*) from payments where payments.customerid = customer.customerid) = 0

You can't do that directly in Alpha, but you could write it in a view.

Another way:

Write a trigger so that when a payment gets updated, it updates a total paid field in the customer.

Then it's just

select * from customer where totalpaid = 0

OR...

create a customer view with the sum of the total paid in the view. Then ...

select * from view_customers where totalpaid = 0

[GGandhi]

Re: unequally yoked: custom sql statement in a list control

In general the default join that alpha gives you is the inner join, which will give records that are present in both customer table and payment table then add the condition where field payment = true

Is that not the case?

[lgrupido]

Re: unequally yoked: custom sql statement in a list control

Yes, but you can override the type of join. I've never tried to do a Not join directly in Alpha.

[GGandhi]

Re: unequally yoked: custom sql statement in a list control

Yes, but you can override the type of join. I've never tried to do a Not join directly in Alpha.

Of course, you can. But why, when the result needed is all the records from customer that has child payment records: will be inner join then add the filter. You need outer joins when you need all the records from customer (left outer) or all from payments (right outer), but that is not the case here, if I understood the question. I could be wrong.

[lgrupido]

Re: unequally yoked: custom sql statement in a list control

Well, if you read his post very carefully, you'll see that he says two entirely different things at the same time:

line 1) All customer records do not have a child record in the payment table.
line 2) Want to retrieve all customer records except those that do have a child record in the payment table where pay = True
line 3) So the result set of records will exclude any customers that were already paid.

I'm assuming he wants what he says in line 1 and 3, not what he says in line 2.