mORMot Open Source

noobies

Member

Registered: 2011-09-13

Posts: 139

query for 2 table

select all disease from table - code work very well

if CompareText(VarName, 'AllDSA00B99') = 0 then Value :=
  Database.List([TSQLPersonDiagnoses], 'distinct Dest', 'dest between 1 and 459').RowCount;

but if I try to merge the choice of two tables in one query:

  if CompareText(VarName, 'AllMDSA00B99') = 0 then Value :=
    Database.List([TSQLPersonDiagnoses, TSQLPatients], 'distinct PersonDiagnoses.Dest', 'PersonDiagnoses.Dest between 1 and 459 and Patients.Sex="М"').RowCount;

I get the same result as in the first query, although the number of patients must be less than the previous query

structure class:

TSQLPerson = class(TSQLRecord)
private
  fFam, fIm, fOt,
  fSex: string;
  ...
end;

TSQLPatients = class(TSQLPerson)
private
  ...
  fDiagnoses: TSQLPersonDiagnoses;
  ...
end;

TSQLPersonDiagnoses = class(TSQLRecordMany)
private
  ...
  fDisNew: Boolean;
  fSource: TSQLPatients;
  fDest: TSQLPersonDiagnose;
  ...
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: query for 2 table

Take a look at the generated SQL query.

You'll have to make a JOIN between both tables in your WHERE parameter.

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: query for 2 table

I tried to write a join condition, but always get the error

Database.List([TSQLPatients, TSQLPersonDiagnoses], 'distinct PersonDiagnoses.Dest', 'Patients inner join PersonDiagnoses on Patients.ID = PersonDiagnoses.Source where PersonDiagnoses.Dest between 1 and 459 and Patients.Sex="М" and DisNew = 1').RowCount;

Database.List([TSQLPatients, TSQLPersonDiagnoses], 'distinct PersonDiagnoses.Dest', 'Patients.ID = PersonDiagnoses.Source where PersonDiagnoses.Dest between 1 and 459 and Patients.Sex="М" and DisNew = 1').RowCount;

Database.List([TSQLPatients, TSQLPersonDiagnoses], 'distinct PersonDiagnoses.Dest from Patients inner join PersonDiagnoses on Patients.ID = PersonDiagnoses.Source', 'PersonDiagnoses.Dest between 1 and 459 and Patients.Sex="М" and DisNew = 1').RowCount;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: query for 2 table

Try to write directly the SQL statement, using SELECT COUNT(*) FROM ...
It will be easier for such queries.

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: query for 2 table

so directly through the sql works fine

Database.ExecuteList([TSQLPatients, TSQLPersonDiagnoses], 'select distinct pds.Dest from Patients p inner join PersonDiagnoses pds on p.ID = pds.Source where pds.Dest between 1 and 459 and p.Sex="М" and DisNew = 1').RowCount;

but I think there is more RESTful method, and that the relationship between the tables automatically be placed in the database structure and uslovich request data. If two class and both are involved in the request, the framework should automatically link the related fields and to construct a query correctly. Or can complement the function of the arguments linking?

Database.ExecuteList([TSQLPatients, TSQLPersonDiagnoses], 'Patients p inner join PersonDiagnoses pds on p.ID = pds.Source', 'distinct pds.Dest', 'pds.Dest between 1 and 459 and p.Sex="М" and DisNew = 1').RowCount;

Last edited by noobies (2011-10-10 06:52:03)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: query for 2 table

IMHO there won't be much interest in using a more ORM-oriented method here to retrieve a count, or only a list of IDs (i.e. pds.Dest).

I suspect you can use the TSQLRecordMany dedicated methods to construct such a query.