I don't know if this is even possible, but wondered if anyone had any ideas.
Let's say I have a table structure as follows
Item
ID
StartDate
EndDate
ItemInfo
ID
ItemID
FieldID
Value
Field
ID
Name
So that for each Item - there could be a dynamic number of ItemInfo entires for each possible Field value.
The problem that I am now facing is trying to sort my ItemCollection sorted by results found in a Field - ItemInfo pair.
For example
I want to retrieve all Items that have contains an ItemInfo value of 'Smith' for the Field with the Name "LastName" - additionally (and this is where I am having problems) I want it sorted by a value from ItemInfo with a Field with the name "State".
I could describe it in a sql with something like
SELECT Item.*, (select ItemInfo.Value from ItemInfo JOIN Field ON ItemInfo.FieldID = Field.FieldID WHERE Field.Name = 'State' AND ItemInfo.ID = Item.ID) AS SortOrder
FROM Item
JOIN ItemInfo ON Item.ItemID = ItemInfo.ItemID
JOIN Field ON ItemInfo.FieldID = Field.FieldID
WHERE ItemInfo.Value = 'Smith'
AND Field.Name = 'LastName'
ORDER BY SortOrder
But I am at a loss for trying to get this into a predicate - I know that I will have to use an alias, but am just unclear on how to go about doing it... Any help would be greatly appreciated.
Thanks and as always thanks for dedication of the forum members and esp. Frans.
Hal