Topic: The SQL query containing nested dependent request hangs up
Faced such problem.
There are conditionally two tables which need to be imported from Access and to unite:
T1 With fields KOD, Name, Date, G1, G2, GX... (About 200 000 records)
T2 With fields KOD, Group, DateIn (about 8 000 records)
Them it is necessary to add in T1 field Group
Join on fields KOD but in 2 this field is not unique, there are variations of records on fields DateIn and required Group.
Therefore one more condition Date = MAX (range DateIn, where DateIn <=Date) should be met
With import to the Ex-fur-tree of problems is not present, problems with join (that in Access that in Eksle)
Wrote such here SQL.
SELECT T1. *, (SELECT TOP 1 Group FROM T2 WHERE T1.KOD=T2.KOD ORDER BY DateIn DESC) as GroupName FROM T1
The nested query in itself works, but as it I interpose in exterior - the request begins working off and hangs up.
In Excell - at once, in Access at attempt to receive all request (and so renders visible lines on the screen, the general kol-in records does not deduce).
Who can knows as to modify request that he earned?
Whether for example it is possible to replace nested query LEFT JOIN? Mind I will not put as it to make. it should not seems that JOIN to hang up.