LEFT JOIN and qualified field names allowed in subform?

LEFT JOIN and qualified field names allowed in subform?

Postby EcoReality » Thu Feb 23, 2017 4:48 pm

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?
:::: Jan Steinman EcoReality Co-op ::::
EcoReality
 
Posts: 26
Joined: Wed Feb 15, 2017 5:20 pm
Location: Salt Spring Island, British Columbia, Canada

Re: LEFT JOIN and qualified field names allowed in subform?

Postby admin » Sat Feb 25, 2017 8:46 am

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
admin
Site Admin
 
Posts: 3348
Joined: Mon Jun 15, 2009 9:53 am

Re: LEFT JOIN and qualified field names allowed in subform?

Postby EcoReality » Mon Mar 20, 2017 2:25 am

admin wrote: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.
:::: Jan Steinman EcoReality Co-op ::::
EcoReality
 
Posts: 26
Joined: Wed Feb 15, 2017 5:20 pm
Location: Salt Spring Island, British Columbia, Canada

Re: LEFT JOIN and qualified field names allowed in subform?

Postby admin » Tue Mar 21, 2017 9:33 am

.
admin
Site Admin
 
Posts: 3348
Joined: Mon Jun 15, 2009 9:53 am


Return to General