4 Replies Latest reply on Mar 25, 2002 7:10 AM by fastball




      How can I do the folowing query using EJBQL?

      select sales.id, products.product, sales.date from
      products, sales where
      (products.id_prod = sales.product)

      Or there is not any possibility to do by EJBQL code?

      Thanks all!

        • 1. Re: EJBQL

          I don't really understand what you are trying to do here. Are you trying to select three random columns from the database so you can iterate over the result set? If so, just use JDBC.

          If this is not what you are trying to do, please clarify the goal of your query.

          • 2. Re: EJBQL

            Hi dsundstrom!

            Thanks for your attention! =)
            Well, about my question, I wants to do a simple join in a relational query, let me explain better... I have two tables: Products and Sales, there is the following fields in each one:

            Products: ID_Prod, product
            Sales: ID, product, date, qtd

            I just need a relationship between these tables...
            Note that I need only a descriptor field (product) on my query...

            The result, of the SQL in the first question could be:

            1---------------PC Keyboard-------------22.03.2002
            2---------------PS/2 Mouse--------------22.03.2002
            3---------------PC HP Vectra------------22.03.2002

            can you help me?

            Thank you very, very much!


            • 3. Re: EJBQL

              Ok, I'm still lost. I think you need to read a book on CMP 2.0. You can get a copy of Mastering EJB free at theserverside.com.

              Any way, yes, you can have a relationship between entities. Yes, you can query across a relationship.

              Where you have me confused is EJB-Ql can only return an collection of a single entity type or a collection of the data from a single cmp field.

              What exactly do you want returned from the query?

              • 4. Re: EJBQL

                Hi dsundstrom!

                Thanks again for your attention!
                Now I can drive my question better...
                Well, I need a relationship between two tables (in the relational terminology).

                One of them is the sales, and the other, contains information about my products...

                As a result of my query, I need only one field of the products table (product, that is the product's name). Note that I have a foreign key on my sales table.

                But, you said that EJBQL can only return an collection of a single entity type or a collection of the data from a single CMP field, so I need to do a relationship between the objects, how can I do this, without create a "products" object with all attributes? Because I need only one... and my products object have 40+ attributes.

                Thanks for the advice, about the book.
                And thank you for the help...
                Any way, I think now I know how to solve this problem...

                thank you!