sorting problem

Classic Servoy
1

Hello, I have a table “contacts” and a table “adresses”. a contact can have many addresses, but on of them is standard. the address_id of the standard address is stored in contacts. there is a relation that points to all addresses and one that points to the standard address.

now i have a list of contacts (table view) that also shows their standard address. some of them have no address at all. when sorting any of the standard address fields, the found set is reduced to the ones that have a standard address. the ones that have no addresses at all are filtered out.
:?:

2

That is expected behaviour in a SQL based system. If childrecords can be non-existent it is recommended to either not sort on them or to not display them in that listview.

3

That is expected behaviour in a SQL based system. If childrecords can be non-existent it is recommended to either not sort on them or to not display them in that listview.

yes and no. to me it seems, that this behaviour depends on the type of join that is done.

when you do something like this:

SELECT firmen.firma AS firma, adressen.strasse AS strasse, adressen.plz AS plz, adressen.ort AS ort
FROM firmen LEFT OUTER JOIN adressen ON firmen.uid_adresse = adressen.uid_adresse ORDER BY adressen.strasse

you get all companies sorted by street, no matter if there is an adress at all.

this leads to an interesting question: wouldn’t it be useful to have an option when defining relationships which gives you control on how the data is collected (of what kind of join is used)?

4

It is correct that with a outer join it is no problem. There are two problems however with outer joins:

  1. Most database have different syntax between each other
  2. Some databases don’t do outer joins at all

We are investigating on Outer join support for the databases that do support it.

5

Has this feature been added in the latest version 3.0? It seems not. If not when will this be implemente?

This feature has been very important mainly for our old customers because they are used to it in their Filemaker version that we are upgrading to Servoy.

Thanks a lot!