Select, join, where - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select, join, where

Author

Topic

akamole
Starting Member

17 Posts

Posted - 2014-03-11 : 10:16:25

Hi

select adress.[Name]
,adress.NLocal AS 'Y'
,adress.ELocal AS 'X'
from [sde_geofir_vansbroB].[gng].[READDRESS] adress
where adress.[RealEstateID] in
(
select a.RealEstateID
from [sde_geofir_vansbroB].[gng].[READDRESS] a
join [sde_geofir_vansbroB].[gng].[RETAXATIONUNIT] b
on b.RealEstateID = a.RealEstateID
where (
b.CodeType='220'
or b.CodeType='221'
or b.CodeType='222'
)
)
will return ~2800 rows.

But i when i try to add following, it will return >1 million rows (or possibly go on forever, i press stop):

select adress.[Name]
,adress.NLocal AS 'Y'
,adress.ELocal AS 'X'
,taxation.CodeType
from [sde_geofir_vansbroB].[gng].[READDRESS] adress, [sde_geofir_vansbroB].[gng].[RETAXATIONUNIT] taxation
where adress.[RealEstateID] in
(
select a.RealEstateID
from [sde_geofir_vansbroB].[gng].[READDRESS] a
join [sde_geofir_vansbroB].[gng].[RETAXATIONUNIT] b
on b.RealEstateID = a.RealEstateID
where (
b.CodeType='220'
or b.CodeType='221'
or b.CodeType='222'
)
)

Why??? Im only adding the column Codetype

remyo
Starting Member

6 Posts

Posted - 2014-03-11 : 10:32:09

You forgot to set the relationship between adress and taxation

ie

from [sde_geofir_vansbroB].[gng].[READDRESS] adress, [sde_geofir_vansbroB].[gng].[RETAXATIONUNIT] taxation
where adress.[RealEstateID] in
(
select a.RealEstateID
from [sde_geofir_vansbroB].[gng].[READDRESS] a
join [sde_geofir_vansbroB].[gng].[RETAXATIONUNIT] b
on b.RealEstateID = a.RealEstateID
where (
b.CodeType='220'
or b.CodeType='221'
or b.CodeType='222'
)
where adress.RealEstateID = taxation.RealEstateID
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources