1

Topic: The SQL query containing nested dependent request hangs up

Excel 2013.
Kind day!
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.

2

Re: The SQL query containing nested dependent request hangs up

To begin with, it is good an index on
T2
(KOD, DateIn) include (Group)

3

Re: The SQL query containing nested dependent request hangs up

aleks222 wrote:

To begin with, it is good an index on
T2
(KOD, DateIn) include (Group)

Thanks. And it is possible more in detail? And that I am not strong in SQL.
At me T1 - the table in Accrss
T2 - A file the Ex-fur-tree.
I can create an index for T2 directly in SQL request?

4

Re: The SQL query containing nested dependent request hangs up

Type couple

SELECT t01. *, t02.*
FROM t1 AS t01, t2 AS t02;
(SELECT t12.KOD, MAX (t12.DateIn) DateIn
FROM t1 AS t11, t2 AS t12
WHERE t11.KOD = t12.KOD
AND t11.Date> = t12.DateIn
GROUP BY t12.KOD
) AS q1
WHERE t01.KOD = t02.KOD
AND t02.KOD = q1.KOD
AND t02.DateIn = q1.DateIn

5

Re: The SQL query containing nested dependent request hangs up

PS. The subquery is better for making static request, and to use it, instead of its text.

6

Re: The SQL query containing nested dependent request hangs up

igor.kovalchuk wrote:

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)

igor.kovalchuk wrote:

At me T1 - the table in Accrss
T2 - a file the Ex-fur-tree.

What of statements truly?

7

Re: The SQL query containing nested dependent request hangs up

igor.kovalchuk wrote:

it is passed...
Thanks. And it is possible more in detail? And that I am not strong in SQL.
At me T1 - the table in Accrss
T2 - A file the Ex-fur-tree.
I can create an index for T2 directly in SQL request?

No. Not .
Import the table  in .
Attach to it an index.
Also will be to you .

8

Re: The SQL query containing nested dependent request hangs up

sdku wrote:

it is passed...
it is passed...
What of statements truly?

As though two in  from a situation:)
The code tested in Accesse - that file T2 connected as  (exterior the data) to basis.
But to implement the code it is necessary all the same in Eksele. And there at me it turns out that T1 I delay Access, and T2 from a file an ex-fur-tree.
T2 Will can change users therefore to retract it in Access there is no sense (on workplaces is not present Access), and T1 - a static file of a database.
Result of join then  in  the pivot table, what with it  (in . and) already could work.

9

Re: The SQL query containing nested dependent request hangs up

igor.kovalchuk;
I hesitate : what values in the field 2.KOD, whence they there undertake and why. WHERE T1.KOD=T2.KOD...

10

Re: The SQL query containing nested dependent request hangs up

Akina wrote:

a type Couple

SELECT t01. *, t02.*
FROM t1 AS t01, t2 AS t02;
(SELECT t12.KOD, MAX (t12.DateIn) DateIn
FROM t1 AS t11, t2 AS t12
WHERE t11.KOD = t12.KOD
AND t11.Date> = t12.DateIn
GROUP BY t12.KOD
) AS q1
WHERE t01.KOD = t02.KOD
AND t02.KOD = q1.KOD
AND t02.DateIn = q1.DateIn

Earned, you the genius! smile

11

Re: The SQL query containing nested dependent request hangs up

I think that is more correct 2 not to connect, and to import. Then  fields KOD and DateIn, and also field type KOD to make same, as in 1.
If the data in Eksele changes - to clear the table and to flood the new.
Besides:
Name Group concerns reserved keywords Jet (as, by the way, Name and Date), therefore in request it is necessary to take it in square brackets;
You are assured, what combinations of values KOD and DateIn are not doubled? Otherwise you SELECT TOP 1 wants to return more than 1 record that in your case is inadmissible;
In request you did not consider condition DateIn <=Date).

12

Re: The SQL query containing nested dependent request hangs up

Anatoly (Kiev);
Thanks, on Group, here , quickly wrote smile therefore as I wrote the interrogation code at a forum not from the original and conditional, with the changed titles for convenience of reading.
Code Akina fulfilled .  I will study the resultant table.
All looks very simply.
There are 3 static tables in  files Acess, in due course them will be more.
I their request through UNION connect and  on sheet in Eksel. She is mine T1
Also I import on other sheet the Ex-fur-tree the table. It T2
But the result of their connection is necessary to me. And here it at me was not took.
Then I push  in a pivot table cache. And all.

13

Re: The SQL query containing nested dependent request hangs up

igor.kovalchuk wrote:

it is passed...
Earned, you the genius! smile

Early I was delighted sad
In general in the field sGroup (I will be it so to name) it is tightened not correctly.
The sense  was that at everyone KD for different Date in T1 it is necessary from T2 to draw out sGroup.
T2 It is necessary to trace relocation KD on groups eventually.
Here so it about all :
T1 T2
KOD Date Name KOD DateIn sGroup Name
1 31.12.2014  1 01.01.1900 
2 31.12.2014  2 01.01.1900 
1 31.12.2015  3 01.01.2016 
2 31.12.2015  1 01.01.2016 
1 31.12.2016 Petja
2 31.12.2016 Masha
3 31.12.2016 Kohl
Expected result
KOD Name Date sGroup
1 Petja 31.12.2014 Tigers
2 Masha 31.12.2014 Lions
1 Petja 31.12.2015 Tigers
2 Masha 31.12.2015 Lions
1 Petja 31.12.2016 Lions
2 Masha 31.12.2016 Lions
3 Kohl 31.12.2016 Eagles
We look at Petja. It in 2016 was moved from one group to another.

14

Re: The SQL query containing nested dependent request hangs up

igor.kovalchuk;
[img=http://s1.radikali.ru/uploads/2017/9/19/d142e00619a38766c0804d76d4879327-full.png]

15

Re: The SQL query containing nested dependent request hangs up

igor.kovalchuk wrote:

it is passed...
Earned, you the genius! smile

Understood, why your variant does not work correctly.
q1  only 1 time only for the first record any most likely with t1.Date And then is scooped from it the data for all remaining records that not is truly as in them t1.Date will be  and  sampling q1 should .
On  to the logician I also pushed  nested query on recalculation in each record that for some reason and veils Eksel/Assess.
Considering that at me t1.Date changes rarely (all some times). Whether It is possible as that forcedly to initiate recalculation q1 at change in the field t1.Date?

16

Re: The SQL query containing nested dependent request hangs up

igor.kovalchuk;
Please, formulate the task of type facing you: there are two tables, at depositing of new record in 1 to enumerate 2 according to the new data, for those records in which N=M new record in 1
(And nevertheless code values in 2- I whence undertake I remember in Yoksele there is no counter-if this field (a cell) is filled with pens-errors are inevitable. Can solve generally all in Yoksele-for what reversal to Access)

17

Re: The SQL query containing nested dependent request hangs up

Tried to rewrite variant Akina with JOIN.
SELECT t01. *, t02. [Group]
FROM ([temp $] as t01 LEFT JOIN [Firms $] as t02 ON t01.KD = t02.KD)
LEFT JOIN (SELECT t12.KD, MAX (t12. [_]) as [God_vhozhdenija]
FROM [temp $] as t11, [Firms $] as t12
WHERE t11.KD = t12.KD
AND CDATE (t11. [sYear])> = CDATE (t12. [_])
GROUP BY t12.KD) as q1
ON (t02.KD = q1.KD)
AND (t02. [God_vhozhdenija] = q1. [God_vhozhdenija])
That works quickly, but again not.
On everyone [KD] at which [Group] in the table changed now there are two doubled records: one with old, bad with new Group.
Help please how to rewrite JOIN, to eliminate ?

18

Re: The SQL query containing nested dependent request hangs up

igor.kovalchuk wrote:

q1  only 1 time only for the first record any most likely with t1.Date And then is scooped from it the data for all remaining records,

Is not present. A problem that you and could not formulate the task normally. In particular, did not specify that field KOD, a pancake, NOT the UNIQUE. Accordingly my request solves absolutely other task.
More shortly - the Test-bd in studio.

19

Re: The SQL query containing nested dependent request hangs up

sdku;
Look. I have a table of basis of statistics T1. Field KOD in it is already filled. It not the counter. Field Date is a date on which the data is presented. (As a matter of fact - the last date year of the reporting, but it is not important)
Table T2. It as a matter of fact the reference manual in which practically for everyone KOD T1 is put down a sign sGroup, and DateIn - date with which this sign to it is appropriated.
I need to unite these two tables so that in e T1 the field sGroup was added and in each record for which T1.KOD=T2.KOD in this field there was a value from T2.sGroup for maximum T2.DateIn reporting T1.Date/not exceeding date
Above I drew the conditional structure of tables and their expected result . For descriptive reasons.

20

Re: The SQL query containing nested dependent request hangs up

Akina wrote:

is shorter - the Test-bd in studio.

21

Re: The SQL query containing nested dependent request hangs up

Akina;
Yes, likely it is unsharp  the task. If KOD was unique I would not need dances with field Date and DateIn.
The test-bd in studio. Approaches, if it is the conditional data on two sheets in  in which the data from source codes will be already flooded? Basically I already unite from sheets in Eksle.
it produces an error because decided to make INNER JOIN. In any way I will not understand why an error.

22

Re: The SQL query containing nested dependent request hangs up

igor.kovalchuk,

23

Re: The SQL query containing nested dependent request hangs up

igor.kovalchuk;
Clumsy example. There Groups are not painted.
.

24

Re: The SQL query containing nested dependent request hangs up

;
Tried. Hangs up: (

25

Re: The SQL query containing nested dependent request hangs up

;
All problem that Accessa on a workplace is not present. For some reason the company without it only resolved office .
And the temporary barracks helps?
I why do under request because a field sYear changes the value and under it each time it is necessary to calculate a field Group as the field kd in the table firms is not unique because of that that records with one kd but different God_vhozhdenija can.