Sorry SQL newbie alert
May have over complicated this (shot myself in the foot) ... have several tables (Dtl<n>) which are filled automatically from a WebSite Form (XML). These tables have both a common set of fields and unique set of fields. Wanting to unify all tables in one Access Form so created a table (Lnk) that links these together via 2 of their common fields
Table(s): dtl<n> common fields are:
FID TEXT (web site form id)
TID TEXT (web site transaction id - unique across forms)
TDATE TEXT DATE DD/MM/YYYY
TTIME TEXT TIME HH:MM:SS (HH = 00 - 23)
UID TEXT (User ID)
Table: lnk fields are:
LID Text = (FID + "-" + TID)
LStatus Text = "Pending, Cancelled, Processed"
LNote Text
PID Text "Process ID"
To get any DTL lnk I use the following Query which works fine
<code>
SELECT * FROM lnk Left JOIN dtl<n> ON lnk.lid = (fid + "-" + tid)
order by lstatus DESC, tdate DESC, ttime DESC;
</code>
What I am looking for is a way, with the Schema's I have, to possibly create one query that just lists:
LID, Lstatus, LNote, PID, LUID, LDATE, LTIME
My expectation is this requires multiple joins such that the lnk table fields always show but only the dtl<n> table fields that the match lnk.lid = (fid + "-" + tid) will show ... I am thinking that this might be achieved with left joins ... but ... its never been clear to me if the subsequent join(s) are attached to the "ROOT" table (lnk) OR if each join is to the previous joined table. (reading the remaining working links to one of the SQL Join FAQS would infer that Joins should be subsequent ... sorry been spoiled by 20+ years of PROGRESS 4GL where table joining always seemed straight forward
<code>
Select lnk.lid, lnk.lnote, lnk.lstatus, lnk.PID,
(dtl1.uid + dtl2.uid + ... + dtl<n>.uid) as LUID,
(dtl1.TDATE + dtl2.TDATE + ... + dtl<n>.TDATE) as LDATE,
(dtl1.TTIME + dtl2.TTIME + ... + dtl<n>.TTIME) as LTIME
FROM lnk Left JOIN dtl1 ON lnk.lid = (dtl1.fid + "-" + dtl1.tid)
Left JOIN dtl2 on lnk.lid = (dtl2.fid + "-" + dtl2.tid)
.
.
.
Left JOIN dtl<n> on lnk.lid = (dtl<n>.fid + "-" + dtl<n>.tid)
order by lMID
</code>
can this easily be done with one query without having to redesign the schema and or writing a function that returns MID by looking up each dtl<n> association
Thanks in advance
May have over complicated this (shot myself in the foot) ... have several tables (Dtl<n>) which are filled automatically from a WebSite Form (XML). These tables have both a common set of fields and unique set of fields. Wanting to unify all tables in one Access Form so created a table (Lnk) that links these together via 2 of their common fields
Table(s): dtl<n> common fields are:
FID TEXT (web site form id)
TID TEXT (web site transaction id - unique across forms)
TDATE TEXT DATE DD/MM/YYYY
TTIME TEXT TIME HH:MM:SS (HH = 00 - 23)
UID TEXT (User ID)
Table: lnk fields are:
LID Text = (FID + "-" + TID)
LStatus Text = "Pending, Cancelled, Processed"
LNote Text
PID Text "Process ID"
To get any DTL lnk I use the following Query which works fine
<code>
SELECT * FROM lnk Left JOIN dtl<n> ON lnk.lid = (fid + "-" + tid)
order by lstatus DESC, tdate DESC, ttime DESC;
</code>
What I am looking for is a way, with the Schema's I have, to possibly create one query that just lists:
LID, Lstatus, LNote, PID, LUID, LDATE, LTIME
My expectation is this requires multiple joins such that the lnk table fields always show but only the dtl<n> table fields that the match lnk.lid = (fid + "-" + tid) will show ... I am thinking that this might be achieved with left joins ... but ... its never been clear to me if the subsequent join(s) are attached to the "ROOT" table (lnk) OR if each join is to the previous joined table. (reading the remaining working links to one of the SQL Join FAQS would infer that Joins should be subsequent ... sorry been spoiled by 20+ years of PROGRESS 4GL where table joining always seemed straight forward
<code>
Select lnk.lid, lnk.lnote, lnk.lstatus, lnk.PID,
(dtl1.uid + dtl2.uid + ... + dtl<n>.uid) as LUID,
(dtl1.TDATE + dtl2.TDATE + ... + dtl<n>.TDATE) as LDATE,
(dtl1.TTIME + dtl2.TTIME + ... + dtl<n>.TTIME) as LTIME
FROM lnk Left JOIN dtl1 ON lnk.lid = (dtl1.fid + "-" + dtl1.tid)
Left JOIN dtl2 on lnk.lid = (dtl2.fid + "-" + dtl2.tid)
.
.
.
Left JOIN dtl<n> on lnk.lid = (dtl<n>.fid + "-" + dtl<n>.tid)
order by lMID
</code>
can this easily be done with one query without having to redesign the schema and or writing a function that returns MID by looking up each dtl<n> association
Thanks in advance
