The nested records are sql server
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 allowed when the subquery follows = ,! =, <, <=,>,> = or when a subquery is used .....
Can anyone explain why this limitation exists?
I got this idea that a multi-user query like this would be good for creating OO objects directly from the database with 1 query
EDIT:
This question is pretty theoretical. To solve this practical problem, I would use a join or just do 2 queries, but I was wondering if there was something preventing you from returning a column as a table (in SQL Server 2008, you can create table types).
Say you have class correlation in your code, think Linq2Sql
public class Table1
{
public int colID,
public string colName,
public List<Table2> table2s;
}
I would like to be able to populate instances of this class directly with 1 request
source to share
You seem to want the recordset (multiple columns and multiple rows) to be returned from table2 for every row in table1. If this is correct, perhaps you can return the data as XML from the DB. Something like that...
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 Table 1, you get multiple sub-nodes in XML for the data in Table 2.
There will likely be performance implications when returning the XML from your database as well as loading the data structure in the front. I don't necessarily assume this is the best approach, but it is probably worth exploring.
source to share
Because the subquery in the select clause must be "inserted" into the column value in every row of the result set from the outer query. You cannot put a set of values in one cell (one column of one row) of a result set.
You need to use inner join. multiple rows returned by the concatenated table will appear as multiple rows in the final result set.
source to share
You would be better off using an INNER JOIN between the two tables and just fetch 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
Remember, however, that the data from Table 1 will be repeated for every comparable record in Table 2. While I believe that the query I posted will get the data in the form you are looking for, I do not think this is the best method for querying the database. I would either run separate queries, or put separate queries in a stored procedure and return multiple result sets.
source to share