SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using multiple columns in subquery with sql server 2000


using multiple columns in subquery with sql server 2000

Author
Message
zwheeler
zwheeler
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40462 Visits: 38567
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'
;



Cool
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)
zwheeler
zwheeler
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 52
thank you. i will try to write in modern times in the future when posting.
mulebhaskarareddy
mulebhaskarareddy
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 12
hi

it will help you

Multiple row/column subqueries in sql server
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search