Hello,

I've checked the document but couldn't find any simple examples about inner join. All I've seen was two tables related in the database with foreign keys, but unfortunately my sql tables doesn't include that. so no relations are pre-defined for any of my tables.

is there any way to fetch a collection of data between two tables (ie orders & orderdetails) for a situation like that?

suppose that's the query i'd like to translate into llblgen style:

SELECT O.*,I.productid FROM Orders O INNER JOIN OrderItems I WHERE O.ShopperID = '99999'

thanks in advance.

notes: my legacy database cannot be reformed with relations. looking for a solution in the codebehind of my UI project. & without editing the project built automatically by llblgen pro.

You can specify the EntityRelation to pass to the fetch method in code, please check the reference manual for EntityRelation CTors.

But if your database doesn't have relations at all, you may define these once and for all in the LLBLGen Pro Designer. (Adding Custom Relations) Rather than having to write code for them each time you gonna need a join.

ok I've added relations between my two tables, but still the code doesn't seem to work, it throws an exception saying that the columns i specified in the resultsetfield could not be bound.

public DataTable test()
{
            ResultsetFields resultsetFields = new ResultsetFields(3);
            resultsetFields.DefineField(OrderGroupFields.OrderNumber,0,"ordermain");
            resultsetFields.DefineField(OrderGroupFields.UserFirstName,1,"ordermain2");
            resultsetFields.DefineField(OrderFormHeaderFields.BankName,2,"orderheader");
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(OrderFormHeaderEntity.Relations.OrderGroupEntityUsingOrdergroupId, "ordermain",
                                 "orderheader", JoinHint.Inner);

            DataTable dynamictable = new DataTable();
            using (adapter = new DataAccessAdapter(strConnectionString))
            {
                try
                {
                    adapter.FetchTypedList(resultsetFields, dynamictable, bucket, 10, true);
                }
                catch (Exception
                     ex) 
                {
                    string k = ex.ToString();
                    throw;
                }
                
            }

            return dynamictable;
}

please help it's driving me crazy - not simply gathering data from two tables which are inner joined.

You aliased the entities in the specification of the relation:

bucket.Relations.Add(OrderFormHeaderEntity.Relations.OrderGroupEntityUsingOrdergroupId, "ordermain", "orderheader", JoinHint.Inner);

however you didn't alias them in the field definitions. You don't need to alias them in the relation specification if the entities aren't equal and they're not in the same inheritance hierarchy. So instead do:

bucket.Relations.Add(OrderFormHeaderEntity.Relations.OrderGroupEntityUsingOrdergroupId, JoinHint.Inner);

Also, please look at the Troubleshooting & debugging section in the using the generated code part of the manual, which gives you insight in how to enable tracing so you can see the SQL queries being generated, which can be of great help to you if you run into this error again (as you then can see the fields refer to the tables and the tables themselves are aliased).

I want to join two tables. but there is no relationship between two like:

SELECT * FROM tblGroups inner join tblPermissions on tblGroups.Id= tblPermissions.EntityId AND tblPermissions.Type='Groups'

basically we are storing different entities permissions in the tblPermissions and EntityId holding ids of different entities

Otis wrote:

You aliased the entities in the specification of the relation:

bucket.Relations.Add(OrderFormHeaderEntity.Relations.OrderGroupEntityUsingOrdergroupId, "ordermain", "orderheader", JoinHint.Inner);

however you didn't alias them in the field definitions. You don't need to alias them in the relation specification if the entities aren't equal and they're not in the same inheritance hierarchy. So instead do:

bucket.Relations.Add(OrderFormHeaderEntity.Relations.OrderGroupEntityUsingOrdergroupId, JoinHint.Inner);

Also, please look at the Troubleshooting & debugging section in the using the generated code part of the manual, which gives you insight in how to enable tracing so you can see the SQL queries being generated, which can be of great help to you if you run into this error again (as you then can see the fields refer to the tables and the tables themselves are aliased).

You can create a relation in code through EntityRelation object, or you can create model-only relationships . Please try that, and come back here if you face problems with that.

P.D. Please don't reopen old threads, instead you can create a new one. Ref: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725