Hi!

This is a question about how to "translate" a sql query for the LLBL Gen object model. I've read in this forum that UNIONs and sub queries are bad and not supported because you can rewrite all of these queries with joins. So we rewrote our query. The result is a query with nested joins:

...
LEFT OUTER JOIN dbo.tbl_VorgabeBausteinKapital kapital 
INNER JOIN dbo.tbl_Vorgabe2VorgabeBaustein v2v_kapital 
ON kapital.lngPK_VorgabeBaustein = v2v_kapital.lngFK_VorgabeBaustein 
ON v2v_bu.lngFK_Vorgabe = v2v_kapital.lngFK_Vorgabe
...

JOINS are added to a query by adding a relation to a relationcollection object. If I understand the system correctly, i have to add a whole relationcollection to another relationcollection to "translate" my nested joins. But this is not possible. Does anybody have an idea how to solve this?

Also Nested Joins can be re-written to be flatened out. Check Frans' post in the following thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9888

Hi and many thanks for your quick resonse!

Sorry, I don't see what you mean. The post you mentioned is about how to add custom predicates to the ON-Clause in the SQL-statement. Do you suggest to use custom predicates to flatten the nested JOINS? If yes, I don't have any idea how. Perhaps you could give me a short example?

So nested joins aren't supported (and bad ), too? Whats the reason for this? Is it bad practice and costly like using UNION or sub queries?

the from clause would read

A 
inner join B on A.Field1 = B.Field1
inner join C on B.Field2 = C.Field2 and A.Field3 = C.Field3

this can be accomplished with a custom relation predicate.

IRelationCollection relations = new RelationsCollection();
relations.Add(AEntity.Relations.Field1BEntityOnField1);
relations.Add(BEntity.Relations.Field2CEntityOnField2);
relations[1].CustomFilter = new PredicateExpression(AFields.Field3 == CFields.Field3);

Hm, I don't get it. We've got the following query and want to remove the nested joins:

SELECT v.*
FROM tbl_Vorgabe v 
INNER JOIN tbl_Vorgabe2VorgabeBaustein v2v_bu 
ON v.lngPK_Vorgabe = v2v_bu.lngFK_Vorgabe 
INNER JOIN tbl_VorgabeBausteinBU bu 
ON bu.lngPK_VorgabeBaustein = v2v_bu.lngFK_VorgabeBaustein 

LEFT OUTER JOIN tbl_Vorgabe2VorgabeBaustein v2v_kapital
INNER JOIN tbl_VorgabeBausteinKapital kapital 
ON v2v_kapital.lngFK_VorgabeBaustein = kapital.lngPK_VorgabeBaustein 
ON v2v_bu.lngFK_Vorgabe = v2v_kapital.lngFK_Vorgabe 

-- LEFT OUTER JOIN tbl_Vorgabe2VorgabeBaustein v2v_kapital 
-- On V.lngPK_Vorgabe = v2v_kapital.lngFK_Vorgabe
-- INNER JOIN tbl_VorgabeBausteinKapital kapital 
-- On v2v_kapital.lngFK_VorgabeBaustein = kapital.lngPK_VorgabeBaustein
-- AND v2v_bu.lngFK_Vorgabe = V.lngPK_Vorgabe

WHERE v2v_kapital.lngPK_Vorgabe2VorgabeBaustein IS NULL

The table relations are: - 1 Vorgabe : n Vorgabe2Vorgabebaustein, - n Vorgabe2VorgabeBaustein : 1 VorgabeBaustein(Kapital/BU)

The lines which are commented out are our approach to rewrite the nested joins. This can't work. We're starting to believe that we MUST use nested joins. But we would be happy to be convinced of the contrary! Thanks in advance for your suggestions and time!

SELECT v.* FROM tbl_Vorgabe v INNER JOIN tbl_Vorgabe2VorgabeBaustein v2v_bu ON v.lngPK_Vorgabe = v2v_bu.lngFK_Vorgabe INNER JOIN tbl_VorgabeBausteinBU bu ON bu.lngPK_VorgabeBaustein = v2v_bu.lngFK_VorgabeBaustein

LEFT OUTER JOIN tbl_Vorgabe2VorgabeBaustein v2v_kapital INNER JOIN tbl_VorgabeBausteinKapital kapital ON v2v_kapital.lngFK_VorgabeBaustein = kapital.lngPK_VorgabeBaustein ON v2v_bu.lngFK_Vorgabe = v2v_kapital.lngFK_Vorgabe

WHERE v2v_kapital.lngPK_Vorgabe2VorgabeBaustein IS NULL

I think the above can be re-written as:

SELECT v.*
FROM tbl_Vorgabe v 
INNER JOIN tbl_Vorgabe2VorgabeBaustein v2v_bu 
ON v.lngPK_Vorgabe = v2v_bu.lngFK_Vorgabe 
INNER JOIN tbl_VorgabeBausteinBU bu 
ON bu.lngPK_VorgabeBaustein = v2v_bu.lngFK_VorgabeBaustein 

LEFT OUTER JOIN tbl_Vorgabe2VorgabeBaustein v2v_kapital
ON v2v_bu.lngFK_Vorgabe = v2v_kapital.lngFK_Vorgabe 

INNER JOIN tbl_VorgabeBausteinKapital kapital 
ON v2v_kapital.lngFK_VorgabeBaustein = kapital.lngPK_VorgabeBaustein 

WHERE v2v_kapital.lngPK_Vorgabe2VorgabeBaustein IS NULL

I didn't understand why you needed the last line of the commented code. And I think you won't need nested JOINS too. (most probably I'm mistaken and there is something obvious that I'm missing)

Hello and thanks for your ideas, We've tried your approach before and did not succeed. The results are not the same. Seems that it's not a proper "translation". The last line of the commented code was a try to rebuild the suggestion from jmeckley.

Would you please explain by resultset example? And would you explain the relation with tbl_VorgabeBausteinKapital?

Would be better if you can re-produce it with more known database samples (eg. Northwind, pubs or AdventureWorks).

So I can match the your results with mine.

Thanks.