Convert multiple SQL code with multiple subqueries into a single query
I'm starting to handle an old database that was generated years ago with ACCESS. All the queries have been designed with the ACCESS query wizard and they seem to be very time consuming and I would like to improve their performance.
All queries depend on at least three subqueries and I would like to rewrite the SQL code to convert them into a single query.
Here you have an example of what I'm talking about:
This is the main query:
SELECT Subquery1.pid, Table4.SIB, Subquery1.event, Subquery1.event_date, Subquery2.GGG, Subquery3.status FROM Subquery1 LEFT JOIN ((Table4 LEFT JOIN Subquery2 ON Table4.SIB = Subquery2.SIB) LEFT JOIN Subquery3 ON Table4.SIB = Subquery3.SIB) ON Subquery1.pid = Table4.PID;
This main query depends on three subqueries:
Subquery1
SELECT Table2.id, Table2.pid, Table2.npid, Table3.event_date, Table3.event, Table3.notes, Table2.other FROM Table2 INNER JOIN Table3 ON Table2.id = Table3.subject_id WHERE (((Table2.pid) Is Not Null) AND ((Table3.event_date)>#XX/XX/XXXX#) AND ((Table3.event) Like "*AAAA" Or (Table3.event)="BBBB")) ORDER BY Table2.pid, Table3.event_date DESC;
Subquery2
SELECT Table1.SIB, IIf(Table1.GGG Like "AAA","BBB", IIf(Table1.GGG Like "CCC","BBB", IIf(Table1.GGG Like "DDD","DDD","EEE"))) AS GGG FROM Table1;
Subquery3
SELECT Table5.SIB, Table5.PID, IIf(Table5.field1 Like "1","ZZZ",IIf(Table5.field1 Like "2","ZZZ",IIf(Table5.field1 Like "3","ZZZ",IIf(Table5.field1 Like "4","HHH",IIf(Table5.field1 Like "5","HHH",IIf(Table5.field1 Like "6","HHH","UUU")))))) AS SSS FROM Table5;
Which would be the best way of improving the performance of this query and converting all the subqueries into a single statement.
I can handle each subquery, but I'm having a hard time joining them together.
Can someone help me a little bit with this code?
Thank you very much,
Best,
If this:
Table5.field1 Like "3"
is really how some of your subqueries are written (without actual wild characters) you can save a lot of time by changing it to
Table5.field1="3"