Hi guys,

I need to re-create this SQL query in LLBL:

SELECT s.Code, s.DisplayString, ss.DisplayString 
FROM System_String s JOIN System_String ss ON s.Code = ss.Code
WHERE s.Language_code = 'en' and ss.Language_code = 'fr'
AND ss.DisplayString = '';

I am joining the "System_String" table onto itself in this code, in order to give me the english string (DisplayString) and the code for any french strings that are empty...

My table looks like this:

System_String (Code, Language_Code, DisplayString)

Can anyone point me in the right direction here - do I need to define a relationship in code to do this??

Thanks!

Matt,

I grabbed this quote from another Forum thread.

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=2709

M:n relations with self are currently not supported (in 1.0.2004.1). If you want I can switch it on in 1.0.2004.2. It was disabled because the runtime engine couldn't alias in the past (before 1.0.2004.1, which wasn't a gui update).

Though as with all relations with self, as you're working on the same table, queries could be more optimial if you simply specify the filters yourself.

If possible, rework your query to not use self joins. Another alternative is to place the self join in a stored procedure.

Do this self join in code by creating an EntityRelation object manually and by specifying the s and ss aliasses.

I tried to make it a single table query, but you then don't get the french data out of it as it's a different set (though from the same table).

Otis wrote:

Do this self join in code by creating an EntityRelation object manually and by specifying the s and ss aliasses.

How do you specify which alias to use in the where clause?

DepartmentEmployeeCollection departmentEmployees = new DepartmentEmployeeCollection();

EntityRelation selfRelation = new EntityRelation(DepartmentEmployeeFields.EmployeeId, DepartmentEmployeeFields.EmployeeId,RelationType.OneToMany);

RelationCollection relation = new RelationCollection();
relation.Add(selfRelation, "a");
relation.Add(EmployeeEntity.Relations.DepartmentEmployeeEntityUsingEmployeeId);

PredicateExpression filter = new PredicateExpression();
***filter.Add(DepartmentEmployeeFields.DepartmentId == CurrentDepartment);
filter.Add(EmployeeFields.Active == 1);

Adding the filter I flagged with the *** causes the where to contain DepartmentEmployee.DeparmentId = 25. What I really need is to use the "a" alias in the where like a.DepartmentId = 25

The above code returns something like this:

SELECT DISTINCT <fields...>
FROM        DepartmentEmployee LPA_a1  
INNER JOIN  DepartmentEmployee  ON  LPA_a1.EmployeeID=DepartmentEmployee.EmployeeID 
INNER JOIN  Employee  ON  Employee.EmployeeID=DepartmentEmployee.EmployeeID 
WHERE       DepartmentEmployee.DepartmentID = 25 
AND         Employee.Active = 1

I need it to look like this or some code that will give me this:

SELECT DISTINCT <fields...>
FROM        DepartmentEmployee LPA_a1  
INNER JOIN  DepartmentEmployee  ON  LPA_a1.EmployeeID=DepartmentEmployee.EmployeeID 
INNER JOIN  Employee  ON  Employee.EmployeeID=DepartmentEmployee.EmployeeID 
WHERE       LPA_a1.DepartmentID = 25 
AND         Employee.Active = 1

Use .SetObjectAlias() on the field in the predicate. So: filter.Add(DepartmentEmployeeFields.DepartmentId.SetObjectAlias("a") == CurrentDepartment);

Otis wrote:

Use .SetObjectAlias() on the field in the predicate. So: filter.Add(DepartmentEmployeeFields.DepartmentId.SetObjectAlias("a") == CurrentDepartment);

Great! Thanks for the info.