... from TableA, TableB; where TableA.KeyField = TableB.KeyFieldA LEFT JOIN (or any outer join, for that matter) is a little more complicated: The WHERE clause only allows an inner join. Records in one table, that don't exist in the other table, will be omitted (i.e., an inner join).
... union; select [fieldlist] from TableA; where KeyField not in (select KeyField from TableB)>Hi all
SELECT 'Accounts J.V. ', t.cBk, t.iNo, t.dDt, ; > (SELECT bOSDrAmt FROM OSAcctDrAmt(s.bDrAmt, uf.bDr, uf.bCr)) AS bBalDr, ; > (SELECT bOSCrAmt FROM OSAcctCrAmt(s.bCrAmt, uf.bDr, uf.bCr)) AS bBalCr, ; > s.mRemarks, t.iID ; > FROM tAccountsJV t ; > JOIN sAccountsJV s ; > ON s.iPID = t.iID ; > AND s.iAcctID = ?piAcctID ; > AND t.dDt <= ?pdDt ; > LEFT JOIN uOSAccounts uf ; > ON uf.iPID = t.iID ; > OR uf.iSID = t.iID>
SET TERM ^ ; > >CREATE PROCEDURE OSACCTDRAMT ( > BDRAMT DOUBLE PRECISION, > BDR DOUBLE PRECISION, > BCR DOUBLE PRECISION) >RETURNS ( > BOSDRAMT DOUBLE PRECISION) >AS >begin > bOSDrAmt = CASE WHEN (bDrAmt > 0 AND bCr > 0) THEN bDrAmt - bCr > WHEN (bDrAmt > 0 AND bDr > 0) THEN bDrAmt - bDr > WHEN (bDr IS NULL AND bCr IS NULL) THEN bDrAmt > ELSE 0 END ; > > suspend; >end >^>
SET TERM ^ ; > >CREATE PROCEDURE OSACCTCRAMT ( > BCRAMT DOUBLE PRECISION, > BDR DOUBLE PRECISION, > BCR DOUBLE PRECISION) >RETURNS ( > BOSCRAMT DOUBLE PRECISION) >AS >begin > bOSCrAmt = CASE WHEN (bCrAmt > 0 AND bDr > 0) THEN bCrAmt - bDr > WHEN (bCrAmt > 0 AND bCr > 0) THEN bCrAmt - bCr > WHEN (bDr IS NULL AND bCr IS NULL) THEN bCrAmt > ELSE 0 END ; > > suspend; >end >^