Nested Query

Posts   
 
    
TogasPoon
User
Posts: 42
Joined: 09-Feb-2006
# Posted on: 08-Jun-2006 18:53:05   

Is this possible to recreate using llblgen?


SELECT 
    GroupId
    ,A.Description
    ,ISNULL(myCount,0) AS 'Count'
FROM Products_Groups A LEFT OUTER JOIN 
    (SELECT ProductGroupId,COUNT(*) AS 'myCount'
    FROM Products_Machines 
    WHERE AvailableId = @AvailableId
    GROUP BY ProductGroupId) B ON A.GroupID = B.ProductGroupId
ORDER BY Count DESC


This gives me


1   aaa   30
2   bbb   10
3   ccc     5
4   ddd     0

I don't think I can the the last record without going the sub query route.

VB 2005 Self Serving 2.0.0.60523

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 09-Jun-2006 08:47:01   

I think it's easier to use a stored procedure for this query. And use the LLBLgen designer to map it's call.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 09-Jun-2006 09:53:10   

Indeed it's not possible because LLBLGen Pro doesn't support FROM (SELECT ... FROM.. ) AS Alias.

Another way to do this is by placing the group by query in a view and map an entity onto it, as well as creating a custom relation

Frans Bouma | Lead developer LLBLGen Pro