1

Topic: Nested queries on one table, I can not win

Good afternoon. There is a big request with JOIN, in it deduce value on the code.
There is a table with the reference manual
ID NAME Parent
1 apple NULL
2 banana 10
3 coco 10
4 pumpkin 20
5 cucumber 20
.......
10 fruit NULL
20 vegetables NULL
Above simply example.
I need to deduce ID, NAME and now it was required PARENT_NAME
That is period of an output the such: 1, a banana, 10, fruit
I cannot connect in the big request an output from a subquery.
I result the code which deduces the list of ancestors - IT works, I receive the unique list of ancestors

select [Voc_water_gorizont].Code_WG, [Voc_water_gorizont].Water_gorizont from [dbo]. [Voc_water_gorizont]
where [dbo]. [Voc_water_gorizont].Code_WG IN
(select [dbo]. [Voc_water_gorizont].Code_parent
from PN_condition
left join [dbo]. [Voc_water_gorizont]
ON PN_condition. Code_WG = [Voc_water_gorizont].Code_wg)

Now it is necessary for me  it to the big request, I do it so:

select
olo = (
select [Voc_water_gorizont].Water_gorizont from [dbo]. [Voc_water_gorizont]
where [dbo]. [Voc_water_gorizont].Code_WG IN
(select [dbo]. [Voc_water_gorizont].Code_parent
from PN_condition
left join [dbo]. [Voc_water_gorizont]
ON PN_condition. Code_WG = [Voc_water_gorizont].Code_wg
)
)

olo - one of fields, them there nearby 20,  as to me to connect lines with  internal sampling I do not understand, the error is produced the such:
The message 512, level 16, state 1, line 1
The nested query returned more than one value. It is forbidden, when the nested query follows after =! =, <<=,>,> = or it is used as expression.
I ask to prompt the correct decision, in advance I thank for

2

Re: Nested queries on one table, I can not win

j-ester wrote:

There is a table with the reference manual
ID NAME Parent
1 apple NULL
2 banana 10
3 coco 10
4 pumpkin 20
5 cucumber 20
.......
10 fruit NULL
20 vegetables NULL
Above simply example.
I need to deduce ID, NAME and now it was required PARENT_NAME
That is period of an output the such: 1, a banana, 10, fruit

SELECT t1.id id, t1.name name, t2.id parent_id, t2.name parent_name
FROM tablename t1
LEFT JOIN tablename t2 ON t1.parent = t2.id

You add this business in the "the big request with JOIN" as one more CTE-source of the data.

3

Re: Nested queries on one table, I can not win

Forgive, I not so expressed. Meant that on the reference manual conduct links from the directory.
The FULL request more low.

select
dbo. PN_catalogue. PN_Number AS ' No hard currency. On  under the register ';
dbo. PN_catalogue. PN_Name AS ' Number on TTS  ',
dbo. PN_catalogue. Num_wateruser AS ' Number on Nedropolzovatelju ';
--dbo. Voc_region. Region AS ' the Subject of the Russian Federation ';
dbo. PN_correspondence. VisualPriv AS ' the Location ',
dbo. PN_correspondence. Shir_grad AS ' .. Hailstones ',
dbo. PN_correspondence. Shir_min AS ' .. Mines ',
dbo. PN_correspondence. Shir_sec AS ' ..  ';
dbo. PN_correspondence. Dolg_grad AS ' Century  hailstones ',
dbo. PN_correspondence. Dolg_min AS ' Century  mines ',
dbo. PN_correspondence. Dolg_sec AS ' Century   ',
dbo. PN_catalogue. AbsOtmUstie AS ' Abs. . A mouth . ';
--NULL AS ' the Index ';
--NULL AS ' Name  ';
dbo. Voc_water_gorizont. WG_index AS ' Index  ';
dbo. Voc_water_gorizont. [Water_gorizont] AS ' Vodonosnyj horizon ';
dbo. Voc_water_gorizont. Code_parent AS ' the Code of ancestor  ';
Federal_WG = (select [Water_gorizont] from [dbo]. [Voc_water_gorizont] t2 where t2.Code_WG = t2.Code_parent);
dbo. PN_regim_sroch_H.Date_zam AS ' Date of sampling of level ';
dbo. PN_regim_sroch_H.Lev AS ' Depth to level  from an earth surface, m ';
dbo. Voc_observ_area. Observ_area_type AS ' the Polygon ',
dbo. PN_regim_sroch_H.God AS ' Year ';
dbo. Voc_cat_NS.Cat_NS AS ' the Observant network ';
dbo. VZ_catalogue. Vodozabor AS ' the Water fence ';
dbo. OI_catalogue. OI_name AS ' Object of extraction'
--select *
FROM dbo. PN_catalogue
left join dbo. Voc_region
ON dbo. PN_catalogue. Code_district = dbo. Voc_region. Code_region
left join dbo. PN_correspondence
ON dbo. PN_catalogue. PN_Number = dbo. PN_correspondence. PN_Number
left join dbo. PN_regim_sroch_H
ON dbo. PN_catalogue. PN_Number = dbo. PN_regim_sroch_H.PN_number
left join dbo. Voc_observ_area
ON dbo. PN_correspondence. Code_observ_area = dbo. Voc_observ_area. Code_observ_area
left join dbo. Voc_cat_NS
ON dbo. PN_catalogue. Code_cat_NS = dbo. Voc_cat_NS.Code_cat_NS
left join dbo. OI_catalogue
ON dbo. PN_correspondence. Code_OI = dbo. OI_catalogue. Code_OI
left join dbo. VZ_catalogue
ON dbo. PN_correspondence. Code_VZ = dbo. VZ_catalogue. Code_VZ
/*  for 2016 with 1610 to 2433, at a binding to PN_condition became 2837*/
left join [dbo].PN_condition
ON dbo. PN_catalogue. PN_Number = dbo. PN_condition. PN_Number
left join Voc_water_gorizont
ON [dbo].PN_condition. Code_wg = Voc_water_gorizont. Code_WG
where dbo. PN_regim_sroch_H.God = @god
AND dbo. PN_correspondence. Code_observ_area IS NOT NULL
GROUP BY
dbo. PN_catalogue. PN_Number;
dbo. PN_catalogue. PN_Name,
dbo. PN_catalogue. Num_wateruser;
dbo. PN_correspondence. VisualPriv,
dbo. PN_correspondence. Shir_grad,
dbo. PN_correspondence. Shir_min,
dbo. PN_correspondence. Shir_sec;
dbo. PN_correspondence. Dolg_grad,
dbo. PN_correspondence. Dolg_min,
dbo. PN_correspondence. Dolg_sec,
dbo. PN_catalogue. AbsOtmUstie;
dbo. Voc_water_gorizont. WG_index;
dbo. Voc_water_gorizont. [Water_gorizont];
dbo. Voc_water_gorizont. Code_parent;
dbo. PN_regim_sroch_H.Date_zam;
dbo. PN_regim_sroch_H.Lev;
dbo. Voc_observ_area. Observ_area_type,
dbo. PN_regim_sroch_H.God;
dbo. Voc_cat_NS.Cat_NS;
dbo. VZ_catalogue. Vodozabor;
dbo. OI_catalogue. OI_name

It is necessary to integrate into it the second request to the reference manual. I do it so:
olo = (
select [Voc_water_gorizont].Water_gorizont from [dbo]. [Voc_water_gorizont]
where [dbo]. [Voc_water_gorizont].Code_WG IN
(select [dbo]. [Voc_water_gorizont].Code_parent
from PN_condition
left join [dbo]. [Voc_water_gorizont]
ON PN_condition. Code_WG = [Voc_water_gorizont].Code_wg
)
)
Where olo - one of fields on which in the end it is possible to make grouping.
I understood your variant, it a little for other case though I now will try

4

Re: Nested queries on one table, I can not win

j-ester wrote:

It is necessary to integrate into it the second request to the reference manual.

Well I like told as...

WITH (my request) AS [reference manual]
-- Further your request
SELECT...
FROM... JOIN [reference manual] ON...
...

5

Re: Nested queries on one table, I can not win

I will add
The latest left join is already connected to this table and deduces the data
left join Voc_water_gorizont
ON [dbo].PN_condition. Code_wg = Voc_water_gorizont. Code_WG
And the codes referring to the reference manual are stored in [dbo].PN_condition
Here on the code [dbo].PN_condition. Code_wg also it is necessary to receive from the reference manual not only value, but also a column of the ancestor and the NAME of the ancestor.
THAT IS in request I so understand request?

6

Re: Nested queries on one table, I can not win

j-ester wrote:

the Latest left join is already connected to this table

Here and replace "this table" with the CTE-table.

7

Re: Nested queries on one table, I can not win

I do not understand, what for here any subqueries?
Who hinders simply to add JOIN in FROM?
And what for conditions in WHERE on the right table LEFT JOIN are superimposed?
Same it turns out INNER JOIN?

8

Re: Nested queries on one table, I can not win

Akina;
Thanks, I not the professional and did not hear before about mechanism CTE, now  and understood about what you.
Thanks for  - I will understand, study, try. Similar that that is necessary.

9

Re: Nested queries on one table, I can not win

iap;
Probably, I above wrote that I am not the developer of a DB, I build requests for output forms as anybody more than it are not able, to a system as I remember from students.
If you specify to me in specific errors and recommendations - I will be very grateful to you. I see that at you many messages and you are explicitly more skilled me in these questions.
In advance thanks

10

Re: Nested queries on one table, I can not win

Akina;
Thanks big, all turned out as I and wanted.
That learned separate thanks for that about such remarkable mechanism as !