LEFT JOIN and qualified field names allowed in subform?

[EcoReality]

I'm doing a many-to-many, using a "join table" as a subform.

I can have many Questionares, and many Crops. A table named "Questionare-Crop" joins them.

The main form is for entering a Questionare. When I get to the Questionare-Crop form, I want to ORDER BY Crop names. So, I have the following SQL in the subform definition:

Code: Select all

SELECT q.Crop, Cultivated_area, Cultivated_area_unit, Metres2, Weight, Weight_unit, Kilograms
FROM Questionare_Crop q
  LEFT JOIN Crops c on q.Crop = c.ID
WHERE q.Questionare = '#RECORD_ID#'
ORDER BY c.Crop

It displays nicely and takes input, but that input is ignored, returning to zero upon save.

The first field is a connection to the Crop table. So I suspect that qualifying the Crop (with "q.Crop") is screwing things up, because the field name "Crop" refers to the crop name (VARCHAR) in the Crops table, and also the crop ID in the Questionare-Crop table. (Yea, I know. My bad.)

Should this sort of thing work? Do I need to "uniquify" the column names to make it work? (Don't wanna do that if I don't have to...)

Is there some other, more "approved" way of ordering subform grids?

[admin]

Jan,

Maybe

Code: Select all

q.Crop

should be

Code: Select all

q.Crop AS crop

and I wouldn't use capital letters in field of table name.

Steven

[EcoReality]

I wouldn't use capital letters in field of table name.

I'm on a case-sensitive file system, and MySQL/MariaDB "leaks" this through to table names, although not field names. So table "Crops" and table "crops" can co-exist on case-sensitive file systems.

I also have a habit from my Smalltalk days, that upper-cased names are proper nouns, and global. I use lower-case table names to distinguish them as "internal use only" (private) tables. Too bad MySQL doesn't have better scoping mechanisms.

[admin]

.