Bruce, There's also the UNION ALL syntax, which basically stacks selects on top of each other. Be sure the first SELECT allows R:BASE to infer the exact correct data type for the ordinal position of each item selected (e.g. TEXT (20)), and use parentheses around columns in later selects that you want R:BASE to cast into the matching data type from above:
SELECT ClientID, InvoiceNumber, InvoiceTotal + FROM Clients c1, Invoices i2 + WHERE c1.ClientID = i2.ClientID + UNION ALL + SELECT ClientID, NULL, (0) + FROM Clients c3 + WHERE NOT EXIST + SELECT clientID from Invoices i4 WHERE i4.ClientID = c3.ClientID) + UNION ALL + SELECT NULL, InvoiceNumber, InvoiceTotal + FROM Invoices i5 + WHERE i5.ClientID IS NULL Bill On Tue, Nov 16, 2010 at 2:35 PM, Bruce Chitiea <[email protected]>wrote: > Larry: > > Between this and the workaround, I'm in great shape. > > Thanks much. > > bruce > > > -------- Original Message -------- > > Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables? > > From: Lawrence Lustig <[email protected]> > > Date: Tue, November 16, 2010 11:21 am > > To: [email protected] (RBASE-L Mailing List) > > > > > > You can perform multiple INNER JOINs in a single statement using the > older > > SQL-89 syntax: > > > > SELECT * FROM table1, table2, table3 + > > WHERE table1.colX = table2.colY AND table2.colA = table3.colB > > > > Unfortunately, this works only for implicit INNER JOINs in the older > syntax. > > For SQL-92 syntax (which is the only way to do OUTER JOINs) you are > limited to > > 2 tables per SELECT statement. The work-around there is to use nested > views as > > described in your message. > > -- > > Larry > > >
