Hello, I have a question related to usage of alias for tables; here is the scenario; I have a single table by the name Locations This table has an columns like ID, Name, ParentID

I need to SELECT something like

ID Name ParentName(Name)

This is done through a query like

SELECT Loc1.ID, Loc1.Name, Loc2.Name FROM Locations Loc1, Locations Loc2 WHERE Loc1.ParentID = Loc2.ID and so... (i.e. a Self Join I guess - simple ain't it)

So I get like

row 1 = 123 - myName - myParentName

I am doing something like;

DataTable dtHotel = new DataTable();

ResultsetFields fields = new ResultsetFields(4);

fields.DefineField(LocationsFieldIndex.ID, 0, "ID", "Locations1"); fields.DefineField(LocationsFieldIndex.Name, 1, "Name", "Locations1"); fields.DefineField(LocationsFieldIndex.Name, 2, "ParentName", "Locations2");

IPredicateExpression filter = new PredicateExpression(); RelationCollection relations = new RelationCollection();

//just a guess relations.Add(LocationsEntity.Relations.LocationsEntityUsingParentID, "Locations1", JoinHint.Inner);

//you can ignore the rest

TypedListDAO dao = new TypedListDAO(); dao.GetMultiAsDataTable(fields, dtHotel, 10000, null, filter, relations, false, null, null, 0, 0);

return dtHotel;

Doesn't work, what am I doing wrong...?

Doesn't work is not descriptive enough. In general you can check the generated query, check the troubleshooting section of the manual under using the generated code section. Hints: You specified 4 in the resultset CTor although you only defined 3 fields Use only one alias in the fields definition, you have 2 use 2 aliases and use that one alias in the specified relation.

Well, I know about the number of fields, the code is just to explain the scenario:

I am just trying to convert the following Query to LLBLGen to return a DataTable:

SELECT Loc1.LongName AS Region, Loc2.LongName AS Country, Loc3.LongName AS City FROM Locations AS Loc3 INNER JOIN Locations AS Loc2 ON Loc3.ParentId = Loc2.LocationId INNER JOIN Locations AS Loc1 ON Loc2.ParentId = Loc1.LocationId

I know I have to use ResultsetFields, IPredicateExpression and RelationCollection but just can't figure out how to handle Loc1, Loc2, Loc3 in relations...

Need a DataTable that has Columns

Region, Country, City

From one table Locations, through relation of ParentId column...

If I am not following the right steps, would you guide me through steps of getting a DataTable as a return for the above query.

Thank You

Please check the rest of my hints. Also the forum contains many similar examples.

Similar Examples? Can I have a link...I needed the complete syntax

Please check the example found in the docs: Creating dynamic lists Also can be done as follows:

ResultsetFields fields = new ResultsetFields(3);

fields.DefineField(LocationsFieldIndex.ID, 0, "ID");
fields.DefineField(LocationsFieldIndex.Name, 1, "Name");
fields.DefineField(LocationsFieldIndex.Name, 2, "ParentName", "parentLocation");

RelationCollection relations = new RelationCollection();
relations.Add(LocationsEntity.Relations.LocationsEntityUsingParentID, "parentLocation", JoinHint.Inner);

Thank You very much, I'll try it out and let you know if there is a problem