2

How can I nest two subqueries and two left joins at the same time? I currently have my query (query1) as below which works perfect.. But it involves connecting two outside queries which have to be updated separtely by data date.

I want to extend query1 by applying subquery (query2) out for practice and to also update everything in one script.

NOTE: Query is written in MS Access.

Any help is much appreciated! 

SELECT DISTINCT 
Acct.AccountNumber
, Acct.ABCat AS Cat
, Acct.fName AS FirstName
, Acct.lName AS LastName
, Acct.Age AS Age
, o.Type, o.OFirstLoginDate
, o.OLastLoginDate
, m.Type
, m.LstDepDate AS LastDepDate
FROM 
table1 AS Acct 
LEFT JOIN 
        OB_table AS o 
        ON Acct.AccountNumber = o.AccountNumber

LEFT JOIN 
        MB_table AS m 
        ON Acct.AccountNumber = m.AccountNumber
WHERE 
Acct.Cat IN (9,12,16,17)
AND Acct.DataDate = 20200430 
AND Acct.Status = 'Open';

Query2 Using Subqueries and Left Join I get the error

Syntax error (missing operator) in query expression 'Acct.AccountNumber = o.AccountNumber    
LEFT JOIN 
(SELECT DISTINCT 
ParentAccount AS AccountNumber
,TYPE
,LstDepDate AS LastDepDate
FROM MBtable
WHERE ProcessDate = 20200430

SELECT DISTINCT 
Acct.AccountNumber
, Acct.ABCat AS Cat
, Acct.fName AS FirstName
, Acct.lName AS LastName
, Acct.Age AS Age
, o.Type, o.OFirstLoginDate
, o.OLastLoginDate
, m.Type
, m.LstDepDate AS LastDepDate
FROM 
table1 AS Acct 
LEFT JOIN 
(
SELECT DISTINCT 
Online.Account AS AccountNumber
, Online.TYPE
, Online.OBFLDate AS OFirstLoginDate
, Online.OBLLDate AS OLastLoginDate
FROM OBtable AS Online
WHERE Online.DataDate=20200430 
And Online.OBFLDate Is Not Null
)
AS o 
ON Acct.AccountNumber = o.AccountNumber
LEFT JOIN
(
SELECT DISTINCT 
ParentAccount AS AccountNumber
, TYPE
, LstDepDate AS LastDepDate
FROM MBtable
WHERE ProcessDate = 20200430 
AND FDate IS NOT NULL
ORDER BY LDate DESC
) 
AS m 
ON Acct.AccountNumber = m.AccountNumber
WHERE Acct.Cat IN (9,12,16,17)
 AND Acct.DataDate=20200430 
AND Acct.Status = 'Open';
Cherrycoke
  • 71
  • 1
  • 1
  • 8
  • Maybe not the only problem (so just a comment), but it jumped out at me that you can't have an ORDER BY inside that 2nd subquery. Otherwise, you need to be a lot more specific about what's wrong than "doesn't work". – Joel Coehoorn May 31 '20 at 02:39
  • 1
    Also, instead of `WHERE Acct.Cat=9 OR ... OR ...` you can say `WHERE Acct.Cat IN (9,12,16,17)` – Joel Coehoorn May 31 '20 at 02:43

1 Answers1

0

You don't give details on the meaning of "does not work". Still I think your case can be a good candidate for the usage of "outer apply", if you use MS SQL Server. Real life example, when to use OUTER / CROSS APPLY in SQL

Máté Gábor
  • 96
  • 5