CODEWITHSUNDEEP
sql-servert-sql
pzaj
pzaj

Reputation: 1092

Multiple selects within one query - one based on another

I'd like to return multiple result sets from a single query with an assumption that the second select is based on first. Basically I need to return:

Table1

ID, Name

Table2

ID, Name, Value, Table1ID

I think joining those tables wouldn't be the case as Table2 may contain several items for the same row from Table1.

Is there any efficient and performance-wise way to do that? Maybe using CTE?

@EDIT

Sample Data:

Table 1:
ID    Name
1     First
2     Second

Table 2:
ID    Value    Table1ID
1     2,25     1
2     2,45     1
3     1,94     2
4     2,12     3

And the result I need is data set (I use C#, so I'll use C# "syntax" here)

DataSet.Tables(0)
    ID    Name
    1     First
    2     Second

DataSet.Tables(1)
    ID    Value    Table1ID
    1     2,25     1
    2     2,45     1
    3     1,94     2

I will have to combine those two tables later on as my class related to Table1 contains list of values. I'm wondering whether I should use SQL Join instead and then convert the result to Lookup in more complex way than I would having two tables returned and having only second table in Lookup instance?

Upvotes: 0

Views: 51

Answers (1)

RoKa
RoKa

Reputation: 160

How about this:

For DataSet.Tables(0):

select ID, Name
from Table1

for DataSet.Tables(1):

select Table2.ID, Table2.Value, Table2.Table1ID
from Table2 
    inner join Table1 on Table2.Table1ID = Table1.ID

Upvotes: 2

Related Questions