Selecting multiple fields in subquery

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

SQL Server 2008 Forums

Transact-SQL (2008)

Selecting multiple fields in subquery

Author

Topic

GustiX
Starting Member

28 Posts

Posted - 2010-07-28 : 11:55:04

Hi

I have a query that I would like to simplify.

It is basicly

select
a,
b,
c,
(select x from sometable ....) as x,
(select y from sometable ....) as y
from
sometable

Since the subqueries are pretty long and complicated and are the same for x and y I would like to be able to do something like this

select
a,
b,
c,
(select x,y from sometable ....) as x,y
from
sometable

This, of course, gives me an error. Is there any neat way of
achieving this?

Sachin.Nand

2937 Posts

Posted - 2010-07-28 : 13:20:28

Is there any relationship between tables x & y?

Please post some sample data & expected o/p.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

GustiX
Starting Member

28 Posts

Posted - 2010-07-28 : 17:30:49

x and y are fields not tables

lets say we have 2 tables

customer
-customer_id
-name

and

address
-customer_id
-street
-city
-created_date

then we create a query

select
customer_id,
name,
(select top 1 street from address where customer_id = c.customer_id order by created_date),
(select top 1 city from address where customer_id = c.customerid order by created_date)
from
customer c

what I would like to be able to do is

select
customer_id,
name,
(select top 1 street, city from address where customer_id = c.customer_id order by created_date)
from
customer c

PavanKK
Starting Member

32 Posts

Posted - 2010-07-29 : 03:22:23

try this...

;WITH cte AS
(
SELECT customer_id
,street
,city
,ROW_NUMBER()OVER(PARTITION BY customer_id ORDER BY created_date) AS Rno
FROM address
)

SELECT C.customer_id
,C.name
,CT.street
,CT.city
FROM customer c
LEFT JOIN cte CT ON C.ProductModelID = CT.ProductModelID AND CT.Rno = 1

KK :)

Sachin.Nand

2937 Posts

Posted - 2010-07-29 : 04:24:10

[code]
select
c.customer_id,
c.name,
T.street,
T.city
from
customer c
Cross Apply
(
select top 1 street, city from address
where customer_id = c.customer_id order by created_date
)T

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources