none
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?!
    Saturday, January 17, 2009 5:49 PM

Answers

  •  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
    • Marked as answer by Bruno_1 Sunday, January 18, 2009 7:39 PM
    Saturday, January 17, 2009 11:12 PM

All replies

  •  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
    • Marked as answer by Bruno_1 Sunday, January 18, 2009 7:39 PM
    Saturday, January 17, 2009 11:12 PM
  • 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
    Sunday, January 18, 2009 6:05 AM
  • 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?!
    Sunday, January 18, 2009 7:39 PM