using multiple columns in subquery with sql server

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

using multiple columns in subquery with sql server

Author

Topic

zwheeler
Starting Member

25 Posts

Posted - 2011-10-13 : 14:28:24

hi
i have a subquery and i want to return two fields i want the two fields to be used in my main query in the where clause. It runs ok with one field but i get a syntax error when i had the 2nd field. Is it possible to return multiple columns in a subquery?

[code="sql"]select *
from tbl_lender_kd_expense ohp, mf_ohp_key_data keydata
where
replace(ohp.FHA_Number,'-','') = keydata.fk_fha_number
and
ohp.project_name = keydata.fk_project_name
and
kd_source_type = 'Net Operating Income'
and (keydata.tag_year,fk_fha_number ) in (Select distinct ohp.year_1,keydata.fk_fha_number from tbl_lender_kd_expense ohp, mf_ohp_key_data keydata
where ohp.year_1 is not null
and replace(ohp.FHA_Number,'-','') = keydata.fk_fha_number
and ohp.kd_source_type = 'Effective Gross Income (from previous table)'
and ohp.project_name = keydata.fk_project_name)[/code]

when i run it with one query i get an error and it tells me that multiple rows exist. however, if i can return both values then it should work as intended.

Any assistance would be greatly appreciated

thank you in advance

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-13 : 15:35:50

You can't do that (as you have discovered). Try turning correlated sub-query into a derived-table (or inline-view) and then join to that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 02:32:37

why not make it a join?


select *
from tbl_lender_kd_expense ohp, mf_ohp_key_data keydata
join (Select distinct ohp.year_1,keydata.fk_fha_number from tbl_lender_kd_expense ohp, mf_ohp_key_data keydata 
where ohp.year_1 is not null 
and replace(ohp.FHA_Number,'-','') = keydata.fk_fha_number 
and ohp.kd_source_type = 'Effective Gross Income (from previous table)'
and ohp.project_name = keydata.fk_project_name) t
on t.year_1 = keydata.tag_year
and t.fk_fha_number =  fk_fha_number 
where
replace(ohp.FHA_Number,'-','') = keydata.fk_fha_number
and 
ohp.project_name = keydata.fk_project_name
and
kd_source_type = 'Net Operating Income'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 02:35:20

why not make it a join?


select *
from tbl_lender_kd_expense ohp, mf_ohp_key_data keydata
join (Select distinct ohp.year_1,keydata.fk_fha_number from tbl_lender_kd_expense ohp, mf_ohp_key_data keydata 
where ohp.year_1 is not null 
and replace(ohp.FHA_Number,'-','') = keydata.fk_fha_number 
and ohp.kd_source_type = 'Effective Gross Income (from previous table)'
and ohp.project_name = keydata.fk_project_name) t
on t.year_1 = keydata.tag_year
and t.fk_fha_number =  fk_fha_number 
where
replace(ohp.FHA_Number,'-','') = keydata.fk_fha_number
and 
ohp.project_name = keydata.fk_project_name
and
kd_source_type = 'Net Operating Income'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources