Go to Main page..

Query with left join (Microsoft SQL Server





Query with left join [new]
Good day.
Please help with the request.
 -- выгрузка без связи с локальным идентификатором select - upload without connection with local identifier
 ph .  "GlobalID" orponid as "GlobalID"
 fil . fil .  as "Филиал" name as "Branch"
 ph . ph  "Адрес" adr_adm_ter as "Address"
 eon . , eon .  "Квартир / офисов" placecnt as "Apartments / offices"
 eon . , eon .  "Количество подъездов" kol_pod as "Number of entrances"
 eon . , eon .  "Этажность" maxfloors as "Floors"
 ( case when eon . areatype = 0 then 'Частный' when eon . areatype = 1 then 'Не частный' end ) as "Тип сектора" , ( case when eon . areatype = 0 then 'Private' when eon . areatype = 1 then 'Not private' end ) as "Sector type"
 from
	 ent_as_house ph 
	 JOIN ENT_OBJECT_NED eon on ph . left JOIN ENT_OBJECT_NED eon on ph .  ob_ned_id = eon .  id
	 JOIN ENT_r_rtk fil on fil . left JOIN ENT_r_rtk fil on fil .  = ph . id = ph .  r_rtk_id
	
 1=1 where 1 = 1
	 ph . and ph .  livestatus = 1
	 ph . and ph .  mrf_id = 354858663 - Siberia
	 ph . and ph .  not null parent_id is not null
	
 by ph . order by ph .  orponid
 ;


 -- выгрузка локальных идентификаторов select - upload local identifiers
 sys .  "HouseID" external_id as "HouseID"
 ph . ph  "GlobalID" orponid as "GlobalID"

 from
       ent_as_house ph 
       ent_id_vs_o_add sys on ph . JOIN ent_id_vs_o_add sys on ph .  = sys . id = sys .  house_id
	
 1=1 where 1 = 1
	 ph . and ph .  livestatus = 1
	 ph . and ph .  mrf_id = 354858663 - Siberia
	 ph . and ph .  not null parent_id is not null
	 sys . and sys .  system_id = 354541532
	 ph . and ph .  1 and 15000000 orponid between 1 and 15000000
 by ph . order by ph .  orponid
 ;


The first request returns all the houses by the specified filters. The second one issues local identifiers only for those houses that were uploaded to external systems.
In the end, I need one table so that the local identifiers pull up on the records of the first query (if there is no local identifier, then the value should be empty). Left Join for some reason does not work.

Tell me, please, how to make a request.
Re: Request with left join [new]
* Ann *,
did not check
+

 ;  AllData AS ( select -- выгрузка без связи с локальным идентификатором ph . orponid as "GlobalID" , fil . name as "Филиал" , ph . adr_adm_ter as "Адрес" , eon . placecnt as "Квартир / офисов" , eon . kol_pod as "Количество подъездов" , eon . maxfloors as "Этажность" , ( case when eon . areatype = 0 then 'Частный' when eon . areatype = 1 then 'Не частный' end ) as "Тип сектора" from ent_as_house ph left JOIN ENT_OBJECT_NED eon on ph . ob_ned_id = eon . id left JOIN ENT_r_rtk fil on fil . id = ph . r_rtk_id where 1=1 and ph . livestatus = 1 and ph . mrf_id = 354858663 --Сибирь and ph . parent_id is not null ), ExData AS ( select -- выгрузка локальных идентификаторов sys . external_id as "HouseID" , ph . orponid as "GlobalID" from ent_as_house ph JOIN ent_id_vs_o_add sys on ph . id = sys . house_id where 1=1 and ph . livestatus =  with AllData AS ( select - upload without connection with the local identifier ph . orponid as "GlobalID" , fil . name as "Branch" , ph . adr_adm_ter as "Address" , eon . placecnt as "Apartments / offices" , eon . kol_pod as "Number of entrances" , eon . maxfloors as "Number of storeys" , ( case when eon . areatype = 0 then 'Private' when eon . areatype = 1 then 'Not private' end ) as "Sector type" from ent_as_house ph left JOIN ENT_OBJECT_NED eon on ph . ob_ned_id = eon . id left JOIN ENT_r_rtk fil on fil . id = ph . r_rtk_id where 1 = 1 and ph . livestatus = 1 and ph . mrf_id = 354858663 - Siberia and ph . parent_id is not null ), ExData AS ( select - upload local identifiers sys . External_id as "HouseID" , ph . Orponid as "GlobalID" from ent_as_house ph JOIN ent_id_vs_o_add sys on ph . Id = sys . House_id where 1 = 1 and ph . Livestatus =  ph . mrf_id = 354858663 --Сибирь and ph . parent_id is not null and sys . system_id = 354541532 and ph . orponid between 1 and 15000000 ) select * from AllData a LEFT JOIn ExData e ON a . 1 and ph . Mrf_id = 354858663 - Siberia and ph . Parent_id is not null and sys . System_id = 354541532 and ph . Orponid between 1 and 15000000 ) select * from AllData a LEFT JOIn ExData e ON a .  GlobalID = e .  BY a . GlobalID ORDER BY a .  GlobalID 

Re: Request with left join [new]
iiyama, thanks for the reply. But it did not help :) too many incomprehensible records are displayed that do not satisfy the filters.
Re: Request with left join [new]
If you insert a subquery into the first request, then the number of records is given the correct number, but the House_id field is empty:

select - upload without connection with local identifier
ph.orponid as "GlobalID"
, (select sys.external_id from ent_id_vs_o_add sys where id = sys.house_id and sys.system_id = 354541532) as "House_id"
....


Tell me, please, where is the error.
Re: Request with left join [new]
All figured out. In the subquery, something was written incorrectly. Thank you all for participating.
Re: Request with left join [new]
No, did not understand: (
The subquery works only on a limited number of records. If you do not limit the number, the error that the subquery returned more than one record ...
Can someone tell me how to do it right?
Re: Request with left join [new]
Here I look at it all and I think ... And I will turn away and everything - the thought has gone. © FD

Something pulls up on local value identifiers, which should be empty. Many obscure records.

Try to more clearly articulate what is happening and what you want to receive, and I am sure you will immediately understand how to do it.
Re: Request with left join [new]
PizzaPizza

There is a table with addresses and there is a table with ID from external systems to which this address was exported. As it turned out, one address can be exported to one external system several times with different IDs. I need to get a list of addresses and IDs of a certain external system. If the address was exported to this system several times, then there should be several records with this address, if not once, then the record should be one with null instead of identifier, if the address was unloaded into another system (not the one that interests us), then the line with the address in the unloading must be present with the value null instead of the identifier.

So far, nothing good happens.
Re: Request with left join [new]
* Ann *
As it turned out, one address can be exported to one external system several times with different IDs.

Well, do you have a key by which you can connect the base record from which you exported and the bases to which you exported?

* Ann *
I need to get a list of addresses and IDs of a certain external system ... there should be several entries with this address

* Ann *
if the address was unloaded into another system, then the line with the address must also be present in the unloading


That is, you need to get a complete selection of all addresses.

To which to make a join (left) sampling from one "external system" by their common key (condition 1)

Anything without a join will be present in the sample (condition 2)

* Ann *
if not once, the entry should be one with null instead of identifier , if the address was unloaded into another system (not the one that interests us), then the same line with the address in the upload should be present with null instead of identifier .


Here you need to clarify: you do not need to distinguish the records from the "specific system" that were not exported and the records that were exported, but to other systems; or if the record was not exported to other systems, then it should not appear in the list at all?
Re: Request with left join [new]
PizzaPizza

I will try to explain with an example:
The address table contains the values:
Address1, Address2, Address3, Address4
There are three external systems: C1, C2, C3

The link table can have the following values:
Address System Identifier in this system
Address1 C1 1111
Address1 C2 1222
Address1 C3 1333
Address2 C2 2222
Address3 C3 3222
Address3 C1 3111
Address3 C1 3112

I need to choose the identifiers of the C1 system, if they are not there, then it should just be a line with the Address, or if the identifier is 2 or more, then the lines with the address should be the same.

Address1 C1 1111
Address2 null null
Address3 C1 3112
Address3 C1 3111
Address4 null null
Re: Request with left join [new]
I tried this:

with AllData as (
select
ph.orponid as GlobalID
, sys.external_id as "HouseID"
, sys.system_id as SystemID
, fil.name as "Branch"
, ph.adr_adm_ter as "Address"
, eon.placecnt as "Apartments / Offices"
, eon.kol_pod as "Number of entrances"
, eon.maxfloors as "Floor"
, (case when eon.areatype = 0 then 'Private' when eon.areatype = 1 then 'Not private' end) as "Sector type"
from
ent_as_house ph
left JOIN ENT_OBJECT_NED eon on ph.ob_ned_id = eon.id
left JOIN ENT_r_rtk fil on fil.id = ph.r_rtk_id
left JOIN ent_id_vs_o_add sys on ph.id = sys.house_id

where 1 = 1
and ph.livestatus = 1
and ph.mrf_id = 354 --Sibir
and ph.parent_id is not null
order by ph.orponid
)

select * from AllData
where systemid = 35454
or systemid is null
order by GlobalID

1. This request returns addresses that were not exported to any system, and those that were exported.
2. But if in the desired system there are two identifiers, then only one of them is issued.
3. If the address was exported to systems other than the one specified in where, then this address is not issued at all.
I need to fix the second and third points. Not yet invented how.
Re: Request with left join [new]
If by your example. There are two tables:

address table
Address
Address 1
Address 2
N address

and table of links
Address system external idi
Address 1 system n id
Address 2 system n id
N address system n id

They are connected only by the Address field.

Choose everything from the address table and the Address field left join the relationship table with the condition field system = something (for example, on table1.Address = table2. Address and table 2.system = c1. You get exactly what you want.

But honestly, this model with the code that you show does not converge.
Re: Request with left join [new]
If you remove too much, you can start with

 select 
 ph .  "Адрес" adr_adm_ter as "Address"
 ph . ph  GlobalID orponid as GlobalID
 sys . sys  "HouseID" external_id as "HouseID"
 sys . sys  SystemID system_id as SystemID


 from
 ent_as_house ph 
 JOIN ent_id_vs_o_add sys on ph . left JOIN ent_id_vs_o_add sys on ph .  = sys . id = sys .  house_id
 sys . And sys .  system_id = 35454

 - where 1 = 1
 --and ph.livestatus = 1
 --and ph.mrf_id = 354 --Sibir
 --and ph.parent_id is not null
 --order by ph.orponid
Re: Request with left join [new]
PizzaPizza

Thank you very much! Your last option has earned, gives all the options. It was necessary just to transfer the filter on the system to the left join.



Old articles you may read here
Go to Main page






ZZZZZZZZZZZZ