Self join

Hi all,

How do you do a self join in SubSonic using the new query object?

Here is the SQL I want to produce

SELECT     Page.ProductId, Page.PageTitle, Page.PageDescription, Page.InActive, Page.NextPageId AS NextPage, Page_1.PageTitle AS NextPageTitle
FROM         Page LEFT OUTER JOIN Page Page_1 ON Page.NextPageId = Page_1.Id

I do not know how you set the alias for the Table name, below is what I cribbed together

ProductBuilder.DB.Select("ProductId",
                                 "PageTitle",
                                 "InActive",
                                 "Page.NextPageId AS NextPage", "Page_1.PageTitle AS NextPageTitle").From("Page").
                                 LeftOuterJoin(ProductBuilder.Page.IdColumn,ProductBuilder.Page.NextPageIdColumn).ExecuteDataSet();

Thanks

Northern

I don't think this is possible with SqlQuery you might have to use InlineQuery for this :(

Cheers, I will use the inline.

Thanks

Yeah, it is not possible, but it's a common scenario in SQL and I've been asking for the same for a couple of months. Table alias feature would be great.

 I offered code for aliasing tables back in March but either the powers that be didn't see my post or they rejected the concept as unnecessary.

-Dennis

Can you post the patch. Thanks Northernmonkey2000

dhigginx:

I offered code for aliasing tables back in March but either the powers that be didn't see my post or they rejected the concept as unnecessary.

Probably the former--especially if it was a forum post (not codeplex).