using multiple columns in subquery with sql server 2000

  • 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?

    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)

    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

  • First, you really should start writing your queries using ANSI-92 style joins instead of ANSI-89 style joins. It makes your code easier to read and separates the join criteria from the filter criteria.

    Try the following. It is untested as you did not provide the DDL for the tables, sample data, or expected results.

    select

    *

    from

    tbl_lender_kd_expense ohp

    inner join mf_ohp_key_data keydata

    on (ohp.project_name = keydata.fk_project_name

    replace(ohp.FHA_Number,'-','') = keydata.fk_fha_number)

    inner join (Select distinct

    ohp.year_1,

    keydata.fk_fha_number

    from

    tbl_lender_kd_expense ohp1

    inner join mf_ohp_key_data keydata1

    on (replace(ohp1.FHA_Number,'-','') = keydata1.fk_fha_number

    and ohp1.project_name = keydata1.fk_project_name

    where

    ohp1.year_1 is not null

    and ohp1.kd_source_type = 'Effective Gross Income (from previous table)') dt

    on (keydata.tag_year = dt.year_1

    and keydata.fk_fha_number = dt.fk_fha_number)

    where

    kd_source_type = 'Net Operating Income'

    ;

  • thank you. i will try to write in modern times in the future when posting.

  • hi

    it will help you

    Multiple row/column subqueries in sql server

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply