Nested records is sql server

Question

select Table1.colID, Table1.colName, 
(select * from Table2 where Table2.colID = Table1.colID) as NestedRows
from Table1

The above query gives you this error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used.....

Can anybody explain why this limitation exist?

I had this idea that this kind of multidimentional queries would be nice for building OO objects directly from the database with 1 query

EDIT:

This question is pretty theoretical. To solve this practical I would use a join or simply done 2 queries, but I wondered if there was anything stopping you from returning a column as a table type (In sql server 2008 you can create table types).

Say you have corrensponding classes in code, think Linq2Sql

public class Table1
{
  public int colID,
  public string colName,
  public List<Table2> table2s;
}

I would like to be able to fill instances of this class directly with 1 query

Answer 1

You would be better off using an INNER JOIN between the two tables and simply selecting the rows you want from each table.

SELECT tab1.colID, tab1.colName, tab2.Column1, tab2.column2
FROM dbo.Table1 AS tab1
    INNER JOIN dbo.Table2 AS tab2
        ON tab1.colID = tab2.colID

However, remember that the data from table1 will be repeated for each matching record in table2. Although I believe the query I posted will get the data in the form you are looking for, I don't think it's the best method for querying the database. I would either execute separate queries, or put the separate queries into a stored procedure and return multiple result sets.

Answer 2

Because the subquery in a select clause must be "inserted" into a column value in every row of the result set from the outer query. You cannot put a set of values into a single cell (a single column of a single row) of the result set.

You need to use an inner join. the multiple rows returned by joined table will be output as multiple rows in the final result set.

Answer 3

I think the query you're looking for is probably:

select Table1.colID, Table1.colName,Table2.*
from Table1 inner join Table2 ON Table1.colID = Table2.colID

Subqueries are more typically used (at least by me) in the WHERE clause.

Answer 4

It appears as though you want a recordset (multiple columns and multiple rows) returned from Table2 for each row in table1. If this is correct, perhaps you could return the data as XML from the DB. Something like this...

select Table1.colID, Table1.colName, Table2.*
from   Table1
       Inner Join Table2
         On Table1.ColId = Table2.ColId
Order By Table1.ColId
For XML Auto

Then, for each row in Table1, you'll get multiple sub-nodes in your XML for table2 data.

There's likely to be performance implications with returning XML from your database, as well as loading your data structure on the front end. I'm not necessarily suggesting this is the best approach, but it's probably worth investigating.