By default joins are generated as:

from t1
join t2 on t1.key = t2.key
join t3 on t2.key = t3.key
...

I'd like to generate my joins as:

from t1
join t2
join t3 on t2.key = t3.key
on t1.key = t2.key
...

This makes a difference when combining inner and outer joins. i.e.

from t1
left join t2 on t1.key = t2.key
inner join t3 on t2.key = t3.key
...

is not the same as

from t1
left join t2
inner join t3 on t2.key = t3.key
on t1.key = t2.key
...

Is what I want to do possible?

The join you are proposing doesn't follow recommended standards. Even if it does execute I'd be suspect that the results are valid and/or consistent.

With that said, take a look at the LLBLGen User Manual. Look for the "Creating dynamic lists" section of the Generated code - Using the typed view and typed list classes, (SelfServicing or Adapter) page. This is an extremely flexible way to join tables using LLBLGen.

Also review the "Custom filters for EntityRelations" section of the Generated Code - filtering and sorting, (SelfServicing or Adapter) for details on adding conditions to your ON clause.

Paul.Lewis wrote:

The join you are proposing doesn't follow recommended standards.

Recommended by whom? How you write the joins determines how the join will be accomplished. You can use a sequential join syntax or a nested join syntax. I don't remember all the symantics but the same query with the same joins using a nested syntax can return different results.

I tried to find some authoritative documentation on this, but I couldn't. I will try to did some up if you are interested.

BOb

This won't work (on all databases): SELECT * FROM Customers C INNER JOIN Orders O LEFT JOIN OrderDetails OD ON C.CustomerID=O.CustomerID ON O.OrderID = OD.OrderID

because it's not ANSI join syntaxis, the ON clause has to be defined directly with the target join operator target pair.

This WILL work: SELECT * FROM Customers C INNER JOIN (Orders O LEFT JOIN OrderDetails OD ON O.OrderID = OD.OrderID) ON C.CustomerID=O.CustomerID

LLBLGen Pro groups every join inside (). So the above join will become: SELECT * FROM (Customers C INNER JOIN Orders O ON C.CustomerID=O.CustomerID) LEFT JOIN OrderDetails OD ON O.OrderID = OD.OrderID

When using LEFT/RIGHT together with INNER, you've to be sure what the order is in which you specify the join, because left/right joins result in different resultsets if their order is different (inner joins don't). What Paul meant to say was that the queries provided as an example matched the first query I listed here, which is illegal syntaxis. LLBLGen Pro generates only pure ANSI join syntaxis: the ON clause is directly specified with the join pair.

So my question to ChicagoKiwi is: could you give a query which works and which illustrates the structure you want? The place of the ON clause isn't important anyway, it's the order in which left/right are specified in the relationcollection.

Otis wrote:

<snip>because it's not ANSI join syntaxis<snip>

I had no idea. This is/was my standard way of writing SQL. You learn something new every day!

Otis wrote:

So my question to ChicagoKiwi is: could you give a query which works and which illustrates the structure you want? The place of the ON clause isn't important anyway, it's the order in which left/right are specified in the relationcollection.

Hypothetical example: I'm trying to return a list of all customers and the number of "rush" subprojects they're associated with. This works (and I think it's ANSI standard), but I'm not sure how to generate it from LLBL.

 select customer_name, count(distinct subproject_customer.subproject_id)
from customer
left join (subproject_customer 
inner join subproject on subproject_customer.subproject_id = subproject.subproject_id and subproject.rush = 'N') 
on customer.cmf = subproject_customer.cmf
group by customer_name

This doesn't work (it only returns customers that have a subproject) and is in the form that LLBL generates:

select customer_name, count(distinct subproject_customer.subproject_id)
from ((customer
left join subproject_customer on customer.cmf = subproject_customer.cmf) 
inner join subproject on subproject_customer.subproject_id = subproject.subproject_id and subproject.rush = 'N') 
group by customer_name 

ChicagoKiwi wrote:

Otis wrote:

So my question to ChicagoKiwi is: could you give a query which works and which illustrates the structure you want? The place of the ON clause isn't important anyway, it's the order in which left/right are specified in the relationcollection.

Hypothetical example: I'm trying to return a list of all customers and the number of "rush" subprojects they're associated with. This works (and I think it's ANSI standard), but I'm not sure how to generate it from LLBL.

 select customer_name, count(distinct subproject_customer.subproject_id)
from customer
left join (subproject_customer 
inner join subproject on subproject_customer.subproject_id = subproject.subproject_id and subproject.rush = 'N') 
on customer.cmf = subproject_customer.cmf
group by customer_name

This doesn't work (it only returns customers that have a subproject) and is in the form that LLBL generates:

select customer_name, count(distinct subproject_customer.subproject_id)
from ((customer
left join subproject_customer on customer.cmf = subproject_customer.cmf) 
inner join subproject on subproject_customer.subproject_id = subproject.subproject_id and subproject.rush = 'N') 
group by customer_name 

Ok, so the INNER join limits the rows, and if that's the last join performed, (joins are done from inner to outer so (a join b) join c will first join a with b and then with c), no rows are returned.

You can reword your working query as:

select customer_name, count(distinct subproject_customer.subproject_id)
from 
(
    subproject_customer inner join subproject 
    on subproject_customer.subproject_id = subproject.subproject_id and subproject.rush = 'N'
) RIGHT JOIN customer
on customer.cmf = subproject_customer.cmf
group by customer_name

so you have to change the order in which you add the relations to the relationcollection