I have a table that is defined as:

DocumentIndexID int | DocumentID int | FieldID int | FieldValue vchar(20)

called tblDocumentIndex.

I'd like to retrieve records which multiple fieldID-FieldValue matches (such as, where fieldID = 2 and fieldValue = "printer1" and fieldID = 3 and fieldValue = "default") and one way was using self-joins. I read some old threads here that said LLBLGen didn't support it at the moment, and was wondering this was still the case. Basically I want to create this sql statement:

SELECT DISTINCT t1.DocumentID

FROM tblDocumentIndex t1

INNER JOIN tblDocumentIndex t2
ON t1.DocumentID = t2.DocumentID

INNER JOIN tblDocumentIndex t3
ON t1.DocumentID = t3.DocumentID

WHERE (t1.FieldID = 1 and t1.FieldValue = 'Epson LX 200')
AND (t2.FieldID = 2 and t2.FieldValue = 'ShipmentForm')
AND (t3.FieldID = 3 and t3.FieldValue = 'Default');

Is it possible? How would you do this, and is there a limitation on the number of inner joins you can do?

There are a couple of ways you can do this. You can setup a typed lists and use aliases for the self joined tables or by using the overload of RelationCollection.Add() which accepts aliases for entity. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3494 Take a look at that thread for an example.

This query would be more efficient:

SELECT DISTINCT t1.DocumentID

FROM tblDocumentIndex t1

INNER JOIN tblDocumentIndex t2
ON t1.DocumentID = t2.DocumentID 
      AND (t2.FieldID = 2 and t2.FieldValue = 'ShipmentForm')

INNER JOIN tblDocumentIndex t3
ON t1.DocumentID = t3.DocumentID 
      AND (t3.FieldID = 3 and t3.FieldValue = 'Default')

WHERE (t1.FieldID = 1 and t1.FieldValue = 'Epson LX 200')