ESQL JOIN not working?

[Justin Close]

I have another ESQL statement that I am having trouble getting to work.  I put this together based on a few references, but I could just have something simple misplaced (I hope).

 

What I am trying to achieve: 

I have two tables, A & B, that are related via A::ID = B::A_ID_FK.  It is 1-to-many (A to B ).  I am trying to find a range of records in B that match a given Start/End dates, and then show those IDs along with the "Type" field from Table A.

ExecuteSQL (
    "SELECT A.Type, B.A_ID_FK,
     FROM A, B
     WHERE A.ID = B.A_ID_FK
        AND B.Date > ?
        AND B.Date < ? "
; "|" ; "" ; $Date_Start ; $Date_End )

Any pointers on this woud be appreciated, either in syntax problems or perhaps other ways of getting the result.

 

Another goal is to achieve this with little-to-no architecture added to the solution for this report, so that's why I would like to stick with using ESQL and scripts.  Ultimately this will pull and show data from a variety of different tables, so that's another reason; also trying to use my first Virtual List technique to do this.  So another part of it is the learning experience. 

 

 

Thanks,

-- J

[Fitch]

Maybe...

SELECT A.Type, B.A_ID_FK,
   FROM A
   INNER JOIN B ON A.ID = B.A_ID_FK
   WHERE 
     B.Date > ?
     AND B.Date < ?

[Justin Close]

Holy dingleberries.  It looks like I had an extra comma in my original statement, the one at the end of the SELECT line. 

 

I removed it and the format I posted above worked fine.  Woe is me.

 

I tried what you suggested, Tom, and that also worked.  Might be worth evaluating which form is faster. 

 

Thanks,

-- J

[Fitch]

Good catch. Glad it worked out.