Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using multiple columns in subquery with sql server 2000 Expand / Collapse
Author
Message
Posted Thursday, October 13, 2011 12:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 28, 2012 2:55 PM
Points: 30, Visits: 52
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
Post #1190119
Posted Thursday, October 13, 2011 1:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
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'
;




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1190138
Posted Thursday, October 13, 2011 1:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 28, 2012 2:55 PM
Points: 30, Visits: 52
thank you. i will try to write in modern times in the future when posting.
Post #1190149
Posted Thursday, June 28, 2012 9:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 6, 2013 3:38 AM
Points: 12, Visits: 12
hi

it will help you

Multiple row/column subqueries in sql server
Post #1322850
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse