Multiple sub query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Multiple sub query

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2006-12-29 : 03:52:40

hi guys,

is it possible to have multiple select subquery in select statement? eg :-

select d.name, d.gen, select (a,b from tblblabla where blabla), d.age

so it would produce 1 line of record :-
name gen a b age
qq 2 2 5 18

thanks in advance

is this possible?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-29 : 04:06:22

No, subqueries used in a select list must return a single value (one row, one column). You'll need to either use multiple subqueries, or join the subquery as a derived table in the FROM clause of the main SELECT, something like

SELECT table1.x, table1.y, dt.a, dt.b
FROM table1
INNER JOIN (SELECT a, b, c FROM table2 WHERE blabla) dt
ON table1.z = dt.c

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2006-12-29 : 06:31:30

thanks,

what if my main select should return 1 row, but when i left join the 2nd table, it becomes 2 rows, because the 2 select return 2 rows.. eg :_

tblfamily:-
name status
cc M

tblfamily:-
name kids
cc gina
cc muna

so how do i get a record like this:-
name status kid1 kid2
cc M gina muna

i know this sound weird, but is this possible :(

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-29 : 07:54:05

It is certainly possible and these kind of queries are called Cross-Tab queries. But with the kind of information you gave, I don't think I can help you more. You need to give some more details.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2006-12-29 : 20:18:36

thanks harsh,
This is what I need to do, i want to return one row/record digest from multiple tables :-

The Output I expect
Id > Item > Lot# > Qty > Udf1 > Udf2
123> Flower> SM1 > 2 >Batch >SLR

tblDetail
Id / Lot# / Qty / Item / Owner
123/ SM1/ 2 / Flower/ Jusco

tblCrossRef
ClientRef / Item / ClientID
AAA / Flower / Jusco

tblPickFormat
ClientRef / PickType
AAA / Batch
AAA / SLR

tblClient
ClientID / Udf1 / Udf2 / Udf3 / Udf4 / Udf5
Jusco / Batch / SLR / PO / Dell / <null>

Okay, how do I perform the inner/left join, when I want to return 1 line of record like the output?
I want to search Id (tblItemDetail) which PickType(tblClient) matches with any of the udf1-udf10 (tblClient) AND the ClientRef & Item (tblCrossRef) match with ClientRef(tblPickFormat) to get the PickType.

Problem is there are 2 picktype for this 1 record of Id, when I join table it will return 2 rows, where as it suppose to return 1 record with 2 type of pickformat.??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources