related field sort problem

Classic Servoy
21

rgansevles:
Some example:

  • orders may have no order details yet (relationship 1-n) and you want to list orders sorted on nr of items → use outer join
  • order detail will always refer to an existing order → use inner joins (the current join type)

Ok, thanks for the clarification (to Patrick, too).

I had problems in displaying customer having more than one address, when searches are performed in addresses table (searching for region, for instance). I guess that, with this option, this problem can be solved.

22

That sounds like a different problem. What exactly did you want to find and what was the problem?

23

patrick:
That sounds like a different problem. What exactly did you want to find and what was the problem?

The problem is that, if a customer has two addresses, I get it twice.
Sometimes this problem can be solved using SELECT DISTINCT, but not always, if I remember well.

24

No. That is not possible. Indeed you have to do a select distinct(). It is logical that you get your customers several times if you don’t distinct their pks…

25

Riccardino:
The problem is that, if a customer has two addresses, I get it twice.
Sometimes this problem can be solved using SELECT DISTINCT, but not always, if I remember well.

If you only select the PK column then you won’t have this problem unless you select other columns as well like the address. Then the PK/address combo makes it unique and you (can) end up with double PK’s.
But for loading a dataset into a controller you only need the PK’s.

Hope this helps.

26

patrick:
No. That is not possible. Indeed you have to do a select distinct(). It is logical that you get your customers several times if you don’t distinct their pks…

I agree it’s logical :slight_smile:
But in some solution the search form allows to search in customers table OR in addresses table: when I search in customers I can use Servoy built-in search, but when I search addresses I need to build my own SQL.
Which means more work for me :slight_smile:

27

ROCLASI:
If you only select the PK column then you won’t have this problem unless you select other columns as well like the address. Then the PK/address combo makes it unique and you (can) end up with double PK’s.
But for loading a dataset into a controller you only need the PK’s.

Hope this helps.

I’ll give another check to the SQL I was using (it was long time ago and I don’t remember well). Maybe I’m using the wrong query (or it can be done better).