Example: How to build a subquery

From: André Luiz N Sousa (andresous..aixaseguros.com.br)
Date: Mon Jul 21 2003 - 09:46:39 EDT

  • Next message: Andrus Adamchik: "Re: Example: How to build a subquery"

    Hello, Andrus,

            I'm having some doubts about how to build a query with a subquery using the Cayenne. Let's see if someone could help me.

            Given the following scenario:

    Entity:
            Escotista (DB Table: TB01_ESCOTISTA)
            =======
            ideEscotista (PK) - it wasn't mapped by the Cayenne Modeller
            nomEscotista String
            dthNascimento Date
            .... (a lot of another fields...)
            MembroPatrulhaArray 'To-Many' relationship to entity "MembroPatrulha"

    Entity:
            MembroPatrulha (DB Table: TB36_MEMBRO_PATRULHA)
            =============
            idePatrulhaFk35 Integer (PK)
            ideEscotistaFk01 Integer (PK)
            dthAdmissao Date
            dthDesligamento Date
            indCargoAtual String
            toEscotista 'To-One' relationship to entity "Escotista"

            Suppose that I have the "idePatrulhaFk35" value and I'd like to list all the records of the "Escotista" entity that don't exist yet in the "MembroPatrulha" for the given "idePatrulhaFk35" value.

            The pure SQL would be something like this:
                "select T01.*
                    from TB01_ESCOTISTA As T01
                  where T01.IDE_ESCOTISTA not in (select T02.IDE_ESCOTISTA_FK01
                                                                                      from TB36_MEMBRO_PATRULHA As T02
                                                                                   where T02.IDE_PATRULHA_FK35 = X);"

            The question is: How can I do that query using the Cayenne syntax (Expression, ExpressionFactory, etc...) ???

            Thanks for the attention,

    André Luiz do Nascimento Sousa
    System Analist
    Brasília - Brazil

                

    This archive was generated by hypermail 2.0.0 : Mon Jul 21 2003 - 09:44:11 EDT