interesting : find() not referencing right column

Classic Servoy
1

Hello.

I’ve gotten myself into a very interesting situation. Not sure how to resolve.

I have 2 tables with multiple columns in each.
TABLE A (customers)

customer_id (primary key)
type
…
…

TABLE B (orders)

order_id (primary key)
customer_id
type
…
…

So - note there is a column in each table representing ‘type’ and in my case for the particular customer_id in question these tables have different values for type. TableA has a value of NULL and TableB has a value of ‘BUSINESS’. Please ignore the fact that this isn’t the best database design (‘type’ probably shouldn’t be in both tables)… however it is what I was given to work with and I cant modify.

In any case when I run the following find() search(), for rows for customers with TABLEB.type=‘business’ it is not found because the find seems to be searching the ‘type’ column in TableA instead of searching in TableB which is what I want it to do. Since the column name is the same in both tables Im not sure how to reference ‘type’ from ‘tableB’ when using this relationship.
(the relationship customer_to_orders is simply a join on ‘customer_id’

	if(foundset.find()) {
	    // customers_to_orders.type = "BUSINESS"
	    foundset.search();
	}

make sense?
It has to be an easy solution but Im struggling a bit.

Kevin

2

That find returns all customers who have orders of type “BUSINESS”.

3

Thats what I would have thought too! Your confirmation on that made me dig deeper… and I uncovered the problem.

There was a tablefilterparam applied to TABLEB earlier on in the code… and for the row in question the condition was not met thus excluding that row at a higher level.
So when the find() went to get the value of ‘type’ from TABLEB it could not find any row and returned null - and hence the find didn’t pick up the row.

Cool!

4

Excellent.