I have a query which won't work for me. I have the following in the query :-

SELECT MAX(m.inspectionid), e.systemid, firsttested, m.inspDate,  e.equipid, e.equipDesc,
e.swl, m.firstexamyn,
m.firstexamYcorrectInstallyn, m.examWithin6MthYN, m.examWithin12MthYN,
m.InAccWithExamSchemeYN, m.AfterExcepCircsYN, m.IDofDefectPart, m.DefectOfImmDangerYN,
m.couldbedangerby, m.repairtoremedydefect, m.testdetailsaspartofexam, m.inspby,
m.authenticatedby, m.mncehistid, m.nextinsp, m.safetoOperate, m.couldbecomedangeryn, e.print
FROM Equipment e LEFT OUTER JOIN mnceHistory m ON e.SystemID = m.MnceHistID
WHERE e.print = true
GROUP BY e.systemid

I get the message inspectionid field not found.

If I cut the text and run it on the sql page in the database system utility I get a result
table. No doubt I am missing the blindingly obvious somewhere if anyone can assist I'll be
very grateful.

Regards
David

Penny dropped.

It was a query which I was altering, when I used the field editor and deleted all fields
and then added them back the query worked.

David

> SELECT MAX(m.inspectionid), e.systemid, firsttested, m.inspDate,
e.equipid, e.equipDesc,
> e.swl, m.firstexamyn,
> m.firstexamYcorrectInstallyn, m.examWithin6MthYN, m.examWithin12MthYN,
> m.InAccWithExamSchemeYN, m.AfterExcepCircsYN, m.IDofDefectPart,
m.DefectOfImmDangerYN,
> m.couldbedangerby, m.repairtoremedydefect, m.testdetailsaspartofexam,
m.inspby,
> m.authenticatedby, m.mncehistid, m.nextinsp, m.safetoOperate,
m.couldbecomedangeryn, e.print
> FROM Equipment e LEFT OUTER JOIN mnceHistory m ON e.SystemID =
m.MnceHistID
> WHERE e.print = true
> GROUP BY e.systemid

I know you have fixed the field definition problem with this query but I
suspect it may not give you the results you expect.  Presuming that the
relationship from Equipment to mnceHistory is 1 to many selecting
MAX(m.inspectionid) does not ensure that the other non aggregate fields from
mnceHistory will be from the same row as MAX(m.inspectionid).

In a lot (most?) of SQL implementations you are not allowed to include non
aggregate fields which are not part of the GROUP BY clause.

I'm sure Tim will correct me if I'm wrong

John

John,

<< In a lot (most?) of SQL implementations you are not allowed to include
non aggregate fields which are not part of the GROUP BY clause.

I'm sure Tim will correct me if I'm wrong >>

No, you're correct.  DBISAM and EDB are fairly unique/non-standard in that
they allow this, warts and all.

--
Tim Young
Elevate Software
www.elevatesoft.com