Need help on complex query (double nested query with grouping)... "JOIN clause not supported" error
I'm trying to implement a complex query... but I'm encountering some issue 
Here is the SQL version,
SELECT qp2.idpersona,
personaevento.idpersonaprofessionista,
personaevento.idpersonaevento,
qp2.maxdidataevento,
qp2.maxdiprogressivoevento,
personaevento.codmacrostatus,
personaevento.codregistro,
personaevento.numiscrizione,
personaevento.alfaiscrizione
FROM (
SELECT qp1.idpersona,
qp1.maxdidataevento,
MAX(personaevento.progressivoevento) AS maxdiprogressivoevento
FROM (
SELECT personaprofessionista.idpersona,
MAX(personaevento.dataevento) AS maxdidataevento
FROM personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista
WHERE ( ( personaevento.dataevento <=# 11 / 6 / 2009 # ) AND ( personaprofessionista.flginterno = true ) )
GROUP BY personaprofessionista.idpersona
) AS qp1 INNER JOIN
(personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista)
ON qp1.maxdidataevento = personaevento.dataevento AND qp1.idpersona = personaprofessionista.idpersona
GROUP BY qp1.idpersona, qp1.maxdidataevento
) AS qp2 INNER JOIN
(personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista)
ON qp2.maxdiprogressivoevento = personaevento.progressivoevento AND qp2.maxdidataevento = personaevento.dataevento AND qp2.idpersona = personaprofessionista.idpersona
Involved Table: PersonaProfessionista PersonaEvento
The relation: PersonaProfessionista.IdPersonaProfessionista 1 --> n PersonaEvento.IdPersonaProfessionista
The Fields: PersonaProfessionista.IdPersonaProfessionista : Key (Int) PersonaProfessionista.IdPersona : Person unique identifier (Int)
PersonaEvento.IdPersonaProfessionista : hook many PersonaEventoEntity to a single PersonaProfessionistaEntity PersonaEvento.DataEvento: define a date at which some of the useful data changed PersonaEvento.ProgressivoEvento: define an incremental counter unique in a series of PersonaEventoEntity owned by a specific PersonaProfessionistaEntity PersonaEvento.codmacrostatus: Useful data that I need to retrieve PersonaEvento.codregistro: Useful data that I need to retrieve PersonaEvento.numiscrizione: Useful data that I need to retrieve PersonaEvento.alfaiscrizione: Useful data that I need to retrieve
The thing works like that: I have many people in the DB, each person own many PersonaProfessionistaEntity (via PersonaProfessionista.IdPersona) each PersonaProfessionistaEntity own many PersonaEventoEntity each PersonaEvento define some useful data at some point in time. So each PersonaProfessionistaEntity own a series of PersonaEventoEntity, and due to that each PersonaProfessionistaEntity cover a period of time, from a a starting date to an ending date. A single person own many PersonaProfessionistaEntity, but they do not overlap in time.
I need a query that respond to the following question: which was the value of the field PersonaEvento.numiscrizione at the date 15/6/2008? I need to fetch a list of all the people with the required data.
So the Inner query find the DataEvento of the most recent data (respect to the asked date) for each people, the second query looks for the max ProgressivoEvento, because it can happen that we have many data change in a single day, and so the last one is the one that apply. The third external query use the MaxDataEvento and the MaxProgressivoEvento to reach the useful data related to the Person/MaxDataEvento/MaxProgressivoEvento.
Do anyone know a better way for attaining this results?
Anyway, in SQL it's easy... the aforementioned query do exactly that.
I've tried the LINQ way, but I'm a newbie in LINQ, and I've just discovered that it's harder than I tough, MS documentation about LINQ doesen't help, and I don't have time to read a 500 pages book...
So, let's go the hard way, and try the derived table (after all some years ago I've asked for LLBLGen to support derived table exactly for this kind of query 
)
To simplify a bit I've cut the external query, and I'm trying to implement the following SQL:
SELECT qp1.idpersona,
qp1.maxdidataevento,
MAX(personaevento.progressivoevento) AS maxdiprogressivoevento
FROM (
SELECT personaprofessionista.idpersona,
MAX(personaevento.dataevento) AS maxdidataevento
FROM personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista
WHERE ( ( personaevento.dataevento <=# 11 / 6 / 2009 # ) AND ( personaprofessionista.flginterno = true ) )
GROUP BY personaprofessionista.idpersona
) AS qp1 INNER JOIN
(personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista)
ON qp1.maxdidataevento = personaevento.dataevento AND qp1.idpersona = personaprofessionista.idpersona
GROUP BY qp1.idpersona, qp1.maxdidataevento
I've come up with the following code:
Dim a As IDataAccessAdapter = DAL.DataAdapterFactory.GetThreadDataAdapter(enDalDB.eDBDati)
Dim dataPar As Date
dataPar = #11/6/2010#
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Dim fieldsMaxDataEvento As New ResultsetFields(2)
fieldsMaxDataEvento.DefineField(PersonaProfessionistaFields.Idpersona, 0)
fieldsMaxDataEvento.DefineField(PersonaEventoFields.DataEvento.SetAggregateFunction(AggregateFunction.Max), 1)
Dim rpbMaxDataEvento As New RelationPredicateBucket
rpbMaxDataEvento.PredicateExpression.Add(PersonaEventoFields.DataEvento <= dataPar)
rpbMaxDataEvento.PredicateExpression.Add(PersonaProfessionistaFields.FlgInterno = True)
rpbMaxDataEvento.Relations.Add(PersonaProfessionistaEntity.Relations.PersonaEventoEntityUsingIdpersonaProfessionista)
Dim groupByMaxDataEvento As New GroupByCollection(PersonaProfessionistaFields.Idpersona)
Dim queryMaxDataEvento As New DerivedTableDefinition(fieldsMaxDataEvento, "queryMaxDataEvento", rpbMaxDataEvento.PredicateExpression, rpbMaxDataEvento.Relations, groupByMaxDataEvento)
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Dim groupByMaxProgressivo As New GroupByCollection()
groupByMaxProgressivo.Add(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxDataEvento"))
groupByMaxProgressivo.Add(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxDataEvento"))
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Dim relationToQueryMaxDataEvento As New DBDati.RelationClasses.DynamicRelation(
queryMaxDataEvento,
JoinHint.Inner,
EntityType.PersonaProfessionistaEntity,
"",
(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxDataEvento") = PersonaEventoFields.DataEvento) And
(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxDataEvento") = PersonaProfessionistaFields.Idpersona)
)
Dim resultSet As New EntityFields2(3)
resultSet.Add(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxDataEvento"))
resultSet.Add(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxDataEvento"))
resultSet.Add(PersonaEventoFields.ProgressivoEvento.SetAggregateFunction(AggregateFunction.Max))
Dim rpb As New RelationPredicateBucket
rpb.Relations.Add(PersonaProfessionistaEntity.Relations.PersonaEventoEntityUsingIdpersonaProfessionista)
rpb.Relations.Add(relationToQueryMaxDataEvento)
Dim dt As New DataTable
a.FetchTypedList(resultSet, dt, rpb, 0, Nothing, True, groupByMaxProgressivo)
that generate the following query
SELECT [LPA_q1].[Idpersona],
[LPA_q1].[DataEvento],
MAX([PersonaEvento].[ProgressivoEvento]) AS [ProgressivoEvento]
FROM (( [PersonaProfessionista] INNER JOIN [PersonaEvento] ON [PersonaProfessionista].[IDPersonaProfessionista]=[PersonaEvento].[IDPersonaProfessionista])
INNER JOIN (
SELECT [PersonaProfessionista].[IDPersona] AS [Idpersona],
MAX([PersonaEvento].[DataEvento]) AS [DataEvento]
FROM ( [PersonaProfessionista] INNER JOIN [PersonaEvento] ON [PersonaProfessionista].[IDPersonaProfessionista]=[PersonaEvento].[IDPersonaProfessionista])
WHERE ( [PersonaEvento].[DataEvento] <= # 11 / 6 / 2009 # AND [PersonaProfessionista].[flgInterno] = true)
GROUP BY [PersonaProfessionista].[IDPersona]
) [LPA_q1]
ON ( [LPA_q1].[DataEvento] = [PersonaEvento].[DataEvento] AND
[LPA_q1].[Idpersona] = [PersonaProfessionista].[IDPersona]))
GROUP BY [LPA_q1].[Idpersona], [LPA_q1].[DataEvento]
the query seem perfect.... but doesn't run and give an error of "JOIN clause not supported"
with some trial and error I've found that Access engine have some problem with a pair of parentheses.
If I change the following part of the generated SQL (It's the On clause of the last Join)
ON ( [LPA_q1].[DataEvento] = [PersonaEvento].[DataEvento] AND
[LPA_q1].[Idpersona] = [PersonaProfessionista].[IDPersona]))
to
ON [LPA_q1].[DataEvento] = [PersonaEvento].[DataEvento] AND
[LPA_q1].[Idpersona] = [PersonaProfessionista].[IDPersona])
it works.
I've only removed the parentheses around the ON clause 
I'm using LLBLGen 2.6 latest version, VS 2010, VB.Net, .Net 3.5, Database: Access (and SQL Server... but I've not tested the issue on SQL Server)
Now... I don't want to hardcode the query in the DB, It's bad to maintain when you have many customers. I'm not good enough at LINQ to create this query in the LLBLGen to LINQ way.
I'm stuck... What can I do? A fix? A better way to handle this kind of data? The LINQ way? but this will probably generate the same SQL giving the same error...
Thanks, Massimiliano
daelmo wrote:
Hi there,
I will try to reproduce it with a Northwind DB (Access). If you can try too would be helpful. What is your LLBLGen runtime library version? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)
I'm creating a sample project using a reduced version of our DB, it'll going to take less time than trying to reproduce the error on the Northwind DB 
I'll post here the sample project once finished.
Thanks, Massimiliano
daelmo wrote:
Hi there,
I will try to reproduce it with a Northwind DB (Access). If you can try too would be helpful. What is your LLBLGen runtime library version? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)
I've posted the sample project on the HelpDesk forum (I don't like disclosing the structure of our DB )
I've used the same post title "Need help on complex query (double nested query with grouping)... "JOIN clause not supported" error"
Thanks, Massimiliano
Thanks to LLBLGen Support Team. Upon their advice I've created a custom PredicateExpression class that strip the external brackets from the ON clause of the Join. Here's the code:
''' <summary>
''' Remove the most external bracket from the generated QueryText.
''' If the generated QueryText start with a "(" and end with a ")", these brackets will be removed.
''' </summary>
''' <remarks>Useful for predicateExpression in the ON part of the JOIN in Access DB, where external brackets are not always supported</remarks>
Public Class BracketsRemoverPredicateExpression
Inherits PredicateExpression
Public Overrides Function ToQueryText(ByRef uniqueMarker As Integer, ByVal inHavingClause As Boolean) As String
Return RemoveExternalBrackets(MyBase.ToQueryText(uniqueMarker, inHavingClause))
End Function
Private Function RemoveExternalBrackets(ByVal pQueryText As String) As String
pQueryText = pQueryText.Trim
'Trace.WriteLine(pQueryText)
If pQueryText.StartsWith("(") AndAlso pQueryText.EndsWith(")") Then pQueryText = " " & pQueryText.Substring(1, pQueryText.Length - 2).Trim & " "
'Trace.WriteLine(pQueryText)
Return pQueryText
End Function
End Class
Naturally there is some code that use this class only when I'm working on an Access DB
Using DerivedTableDefinition and DynamicRelation with grouping is not the easiest thing in the world so, for the sake of completeness, here is the code for the full SQL (3 nestedselect, 2 using grouping)
Needed SQL
SELECT qp2.idpersona,
personaevento.idpersonaprofessionista,
personaevento.idpersonaevento,
qp2.maxdidataevento,
qp2.maxdiprogressivoevento,
personaevento.codmacrostatus,
personaevento.codregistro,
personaevento.numiscrizione,
personaevento.alfaiscrizione
FROM (
SELECT qp1.idpersona,
qp1.maxdidataevento,
MAX(personaevento.progressivoevento) AS maxdiprogressivoevento
FROM (
SELECT personaprofessionista.idpersona,
MAX(personaevento.dataevento) AS maxdidataevento
FROM personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista
WHERE ( ( personaevento.dataevento <=# 11 / 6 / 2009 # ) AND ( personaprofessionista.flginterno = true ) )
GROUP BY personaprofessionista.idpersona
) AS qp1 INNER JOIN
(personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista)
ON qp1.maxdidataevento = personaevento.dataevento AND qp1.idpersona = personaprofessionista.idpersona
GROUP BY qp1.idpersona, qp1.maxdidataevento
) AS qp2 INNER JOIN
(personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista)
ON qp2.maxdiprogressivoevento = personaevento.progressivoevento AND qp2.maxdidataevento = personaevento.dataevento AND qp2.idpersona = personaprofessionista.idpersona
Generated SQL
SELECT [LPA_q1].[Idpersona],
[PersonaEvento].[IDPersonaProfessionista] AS [IdpersonaProfessionista],
[PersonaEvento].[IDPersonaEvento] AS [IdpersonaEvento],
[LPA_q1].[DataEvento],
[LPA_q1].[ProgressivoEvento],
[PersonaEvento].[CodMacroStatus],
[PersonaEvento].[CodRegistro],
[PersonaEvento].[NumIscrizione],
[PersonaEvento].[AlfaIscrizione]
FROM ( (
[PersonaProfessionista] INNER JOIN [PersonaEvento] ON [PersonaProfessionista].[IDPersonaProfessionista] = [PersonaEvento].[IDPersonaProfessionista]
) INNER JOIN (
SELECT [LPA_q2].[Idpersona], [LPA_q2].[DataEvento], MAX([PersonaEvento].[ProgressivoEvento]) AS [ProgressivoEvento]
FROM (
(
[PersonaProfessionista] INNER JOIN [PersonaEvento] ON [PersonaProfessionista].[IDPersonaProfessionista] = [PersonaEvento].[IDPersonaProfessionista]
) INNER JOIN (
SELECT [PersonaProfessionista].[IDPersona] AS [Idpersona], MAX([PersonaEvento].[DataEvento]) AS [DataEvento]
FROM ( [PersonaProfessionista] INNER JOIN [PersonaEvento] ON [PersonaProfessionista].[IDPersonaProfessionista] = [PersonaEvento].[IDPersonaProfessionista])
WHERE ( [PersonaEvento].[DataEvento] <= @DataEvento1 AND [PersonaProfessionista].[flgInterno] = @FlgInterno2 )
GROUP BY [PersonaProfessionista].[IDPersona]
) [LPA_q2]
ON [LPA_q2].[DataEvento] = [PersonaEvento].[DataEvento] AND
[LPA_q2].[Idpersona] = [PersonaProfessionista].[IDPersona]
)
GROUP BY [LPA_q2].[Idpersona], [LPA_q2].[DataEvento]
) [LPA_q1]
ON [LPA_q1].[DataEvento] = [PersonaEvento].[DataEvento] AND
[LPA_q1].[ProgressivoEvento] = [PersonaEvento].[ProgressivoEvento] AND
[LPA_q1].[Idpersona] = [PersonaProfessionista].[IDPersona]
)
Respect the needed SQL I've changed the order of table/query in join... the original order wasn't working.
here is the VB code
Dim a As IDataAccessAdapter = DAL.DataAdapterFactory.GetThreadDataAdapter(enDalDB.eDBDati)
Dim dataPar As Date
dataPar = #11/6/2010#
'==========================================================================================================================================================
' Internal query
'----------------------------------------------------------------------------------------------------------------------------------------------------------
Dim fieldsMaxDataEvento As New ResultsetFields(2)
fieldsMaxDataEvento.DefineField(PersonaProfessionistaFields.Idpersona, 0)
fieldsMaxDataEvento.DefineField(PersonaEventoFields.DataEvento.SetAggregateFunction(AggregateFunction.Max), 1)
Dim rpbMaxDataEvento As New RelationPredicateBucket
rpbMaxDataEvento.PredicateExpression.Add(PersonaEventoFields.DataEvento <= dataPar)
rpbMaxDataEvento.PredicateExpression.Add(PersonaProfessionistaFields.FlgInterno = True)
rpbMaxDataEvento.Relations.Add(PersonaProfessionistaEntity.Relations.PersonaEventoEntityUsingIdpersonaProfessionista)
Dim groupByMaxDataEvento As New GroupByCollection(PersonaProfessionistaFields.Idpersona)
Dim queryMaxDataEvento As New DerivedTableDefinition(fieldsMaxDataEvento, "queryMaxDataEvento",
rpbMaxDataEvento.PredicateExpression,
rpbMaxDataEvento.Relations,
groupByMaxDataEvento)
'==========================================================================================================================================================
'==========================================================================================================================================================
' Relation to join the internal query with the central query
'----------------------------------------------------------------------------------------------------------------------------------------------------------
'the PredicateExpression joinONqueryMaxDataEvento is needed due to access not supporting brackets in fome JOIN clause
Dim joinONqueryMaxDataEvento As PredicateExpression
If DAL.DalConfig.DBDatiDatabaseType = enDbType.eAccess Then
'DB is Access, I need to strip the external brackets of the ON part of the JOIN clause (access doesent support brackets in this query)
joinONqueryMaxDataEvento = New BracketsRemoverPredicateExpression
Else
'It's not Access, let's LLBLGen do it's job as usal
joinONqueryMaxDataEvento = New PredicateExpression
End If
joinONqueryMaxDataEvento.AddWithAnd(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxDataEvento") = PersonaEventoFields.DataEvento)
joinONqueryMaxDataEvento.AddWithAnd(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxDataEvento") = PersonaProfessionistaFields.Idpersona)
Dim relationToQueryMaxDataEvento As New DBDati.RelationClasses.DynamicRelation(
queryMaxDataEvento,
JoinHint.Inner,
EntityType.PersonaProfessionistaEntity,
"",
joinONqueryMaxDataEvento
)
'==========================================================================================================================================================
'==========================================================================================================================================================
' Central query
'----------------------------------------------------------------------------------------------------------------------------------------------------------
Dim fieldsMaxProgressivo As New ResultsetFields(3)
fieldsMaxProgressivo.DefineField(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxDataEvento"), 0)
fieldsMaxProgressivo.DefineField(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxDataEvento"), 1)
fieldsMaxProgressivo.DefineField(PersonaEventoFields.ProgressivoEvento.SetAggregateFunction(AggregateFunction.Max), 2)
Dim rpbMaxProgressivo As New RelationPredicateBucket
'note that the order in wich the relation are added to the rpbMaxProgressivo matter, if you change the order it dosen't works
rpbMaxProgressivo.Relations.Add(PersonaProfessionistaEntity.Relations.PersonaEventoEntityUsingIdpersonaProfessionista)
rpbMaxProgressivo.Relations.Add(relationToQueryMaxDataEvento)
Dim groupByMaxProgressivo As New GroupByCollection()
groupByMaxProgressivo.Add(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxDataEvento"))
groupByMaxProgressivo.Add(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxDataEvento"))
Dim queryMaxProgressivo As New DerivedTableDefinition(fieldsMaxProgressivo, "queryMaxProgressivo",
rpbMaxProgressivo.PredicateExpression,
rpbMaxProgressivo.Relations,
groupByMaxProgressivo)
'==========================================================================================================================================================
'==========================================================================================================================================================
' Relation to join the central query with the external query
'----------------------------------------------------------------------------------------------------------------------------------------------------------
'the PredicateExpression joinONqueryMaxProgressivo is needed due to access not supporting brackets in fome JOIN clause
Dim joinONqueryMaxProgressivo As PredicateExpression
If DAL.DalConfig.DBDatiDatabaseType = enDbType.eAccess Then
'DB is Access, I need to strip the external brackets of the ON part of the JOIN clause (access doesent support brackets in this query)
joinONqueryMaxProgressivo = New BracketsRemoverPredicateExpression
Else
'It's not Access, let's LLBLGen do it's job as usal
joinONqueryMaxProgressivo = New PredicateExpression
End If
joinONqueryMaxProgressivo.AddWithAnd(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxProgressivo") = PersonaEventoFields.DataEvento)
joinONqueryMaxProgressivo.AddWithAnd(PersonaEventoFields.ProgressivoEvento.SetObjectAlias("queryMaxProgressivo") = PersonaEventoFields.ProgressivoEvento)
joinONqueryMaxProgressivo.AddWithAnd(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxProgressivo") = PersonaProfessionistaFields.Idpersona)
Dim relationToQueryMaxProgressivo As New DBDati.RelationClasses.DynamicRelation(
queryMaxProgressivo,
JoinHint.Inner,
EntityType.PersonaProfessionistaEntity,
"",
joinONqueryMaxProgressivo
)
'==========================================================================================================================================================
'==========================================================================================================================================================
' External query
'----------------------------------------------------------------------------------------------------------------------------------------------------------
Dim resultSet As New EntityFields2(1)
resultSet.Add(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxProgressivo"))
resultSet.Add(PersonaEventoFields.IdpersonaProfessionista)
resultSet.Add(PersonaEventoFields.IdpersonaEvento)
resultSet.Add(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxProgressivo"))
resultSet.Add(PersonaEventoFields.ProgressivoEvento.SetObjectAlias("queryMaxProgressivo"))
resultSet.Add(PersonaEventoFields.CodMacroStatus)
resultSet.Add(PersonaEventoFields.CodRegistro)
resultSet.Add(PersonaEventoFields.NumIscrizione)
resultSet.Add(PersonaEventoFields.AlfaIscrizione)
Dim rpb As New RelationPredicateBucket
'note that the order in wich the relation are added to the RPB matter, if you change the order it dosen't works
rpb.Relations.Add(PersonaProfessionistaEntity.Relations.PersonaEventoEntityUsingIdpersonaProfessionista)
rpb.Relations.Add(relationToQueryMaxProgressivo)
Dim dt As New DataTable
a.FetchTypedList(resultSet, dt, rpb)
'==========================================================================================================================================================
...and it works like a charm