On Nov 29, 2003, at 1:05 PM, Norman Palardy wrote:
On 29-Nov-03, at 10:22 AM, Dr. Leif Lensgraf wrote:
This will WORK.
sqlST ="select M._rowid, M.MsgDate, M.ToID, M.FromID, M.Msg,
M.Done, M.DateNeeded,"
sqlST = sqlST +" T.ToWho, F.FromWho"
sqlST = sqlST+" from msgTBL as M,"
sqlST = sqlST+" ToTBL as T, FromTBL as F"
sqlST = sqlST+" where M.ToID=T._rowid "
sqlST = sqlST+" and
M._rowid='"+reportListLB.cell(reportListLB.listindex,4)+"'"
// reportListLB 4th col has the rowid for MsgTBL
rs = db.SQLSelect(sqlST)
and this will work...
sqlST ="select M._rowid, M.MsgDate, M.ToID, M.FromID, M.Msg,
M.Done, M.DateNeeded,"
sqlST = sqlST +" T.ToWho, F.FromWho"
sqlST = sqlST+" from msgTBL as M,"
sqlST = sqlST+" ToTBL as T, FromTBL as F"
sqlST = sqlST+" where M.FromID=F._rowid "
sqlST = sqlST+" and
M._rowid='"+reportListLB.cell(reportListLB.listindex,4)+"'"
// reportListLB 4th col has the rowid for MsgTBL
rs = db.SQLSelect(sqlST)
but this will not
sqlST ="select M._rowid, M.MsgDate, M.ToID, M.FromID, M.Msg,
M.Done, M.DateNeeded,"
sqlST = sqlST +" T.ToWho, F.FromWho"
sqlST = sqlST+" from msgTBL as M,"
sqlST = sqlST+" ToTBL as T, FromTBL as F"
sqlST = sqlST+" where M.ToID=T._rowid "
sqlST = sqlST+" and M.FromID=F._rowid "
sqlST = sqlST+" and
M._rowid='"+reportListLB.cell(reportListLB.listindex,4)+"'"
// reportListLB 4th col has the rowid for MsgTBL
rs = db.SQLSelect(sqlST)
The problem comes when trying to tie in the "third" table...all the
need info for relating the 3 tables (FromTBL, ToTBL, MsgTBL) together
is stored in the MsgTBL. MsgTBL has fields called ToID and FromID
these store the rowid of ToTBL and FromTBL records that relate to the
Msg record. I would like to do this with one SQL statement as I
understand it should be able to be done? But I'm not sure where to
go from here...except breaking down the query into multi statements
and storing the intermediate data in RB var's...which means more
overhead..although for my small project this shouldn't mean all that
much...
The original query you had should return those that do match. The
query is fine.
Thanks, I was beginning to wonder about my sanity....
Analyzing your data to see if in fact there is a row in msgTBL that
should have a match in both the ToTBL & FromTBL would be my next step.
Well, the I know that the data is in the tables as I can open the
database up and look it.
Maybe someone out there can throw together a quick table database and
verify that this is a bug?
IF there is, and you still do not get results, then I'd suggest filing
a bug report as this should work.
- - -
Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
- - -
Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
|