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?
from tbl_lender_kd_expense ohp, mf_ohp_key_data keydata
replace(ohp.FHA_Number,'-','') = keydata.fk_fha_number
ohp.project_name = keydata.fk_project_name
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)
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