Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Question

I have 2 tables with a parent-child relation, i want to query all rows from parent, with the first child for each row. each row in parents table have multiple associated rows in childs table.

something that doesn't work and give me the error in title is:
select parents.id, parent.content, (select top 1 child.id, child.content from childs where childs=parents.id)
from parents where ....

Now of course i can get the child fields column by column, but i wonder does this mean more processing (for each fetch a new scan?). I tried using joins but it seems really hard to get what i need, i thought also about using a cursoron childs table to get "first rows per parent" then join the result with the parents table but also this seems little odd and unnecessary, what way should i take ?

Thanks

---

What should i put here?!

Answer 1

 Hi Bruno,

This would work, and doesn't incur the (potentially) additional cost of a correlated subquery:

select          p.id, p.content, ch.ID, ch.Content

from            parents p

LEFT OUTER JOIN (SELECT     ParentID, MAX(id) AS ID, MAX(content) AS Content

FROM        children

GROUP BY    ParentID) AS ch ON p.id = ch.ParentID

where ....

Alternatively, this would also work on SQL 2005 and 2008:

;WITH OrderedChildren (ParentID, ID, Content, RowNumber)

AS

(SELECT ParentID, ID, Content,

row_number() OVER (PARTITION BY ParentID ORDER BY ID) AS RowNumber

FROM    Children)

SELECT          p.ID, p.Content, oc.ID, oc.Content

FROM            Parents p

LEFT OUTER JOIN OrderedChildren oc ON p.ID = oc.ParentID AND oc.RowNumber = 1

Does this help?

---

Aaron Alton | thehobt.blogspot.com

Answer 2

When you do something like this:

select parents.id, parent.content, (select top 1 child.id, child.content from childs where childs=parents.id)
from parents where ....

It will always be treated like a scalar expression/subquery. You can't return > 1 value in a query like that.  Also, when you do TOP 1, this will just return a  row, not the first row.

Aaron's solution with max will give you values, but do you want a consistent row, or just the two values?

In 205 and later I think you should be able to use a derived table and row_number() to do this:

select parents.id, parent.content
from parents --cross apply allows the left input to inject values into the right (in this case a derived table)
             cross apply (select child.id, child.content, row_number() over (order by <something>) as row_nbr
                                  from childs 
                                  where child.parentId=parents.id  --strongly consider not using ID, and name childId, parentId
                                                                                         --easier to work with in queries
                       ) as childRows
where  row_nbr = 1

---

http://drsql.spaces.msn.com

Answer 3

Unfortunately the database I am working on is under SQL2000, using rowNumber is not an option. Now for

select          p.id, p.content, ch.ID, ch.Content

from            parents p

LEFT OUTER JOIN (SELECT     ParentID, MAX(id) AS ID, MAX(content) AS Content

FROM        children

GROUP BY    ParentID) AS ch ON p.id = ch.ParentID

where ....

The problem here is that content is varchar, i don't need MAX(content), but the associated content for the MAX(id), i think it can be done bit a double scan, first i get the ids of top rows then i join children with the result of subquery. The subquery will have much less rows than the original table so i guess this will improve performance considerably. However, it seems little strange that theer is no direct way top get such resultset under sql2000 directly

Thanks for everyone contributed.

---

What should i put here?!