-----Original Message-----
From: Thomas Fischer [mailto:[email protected]]
Sent: den 2 mars 2009 14:54
To: Apache Torque Users List
Subject: RE: Multiple addJoin with OR
> > I want to create an SQLQuery that combines data from three tables.
Right
> > now, my javacode looks like this:
> >
> > Criteria.addJoin(TableA.id, TableB.fk);
> > Criteria.addJoin(TableA.id, TableC.fk);
> >
> > This results in this SQL query:
> >
> > WHERE TableA.id = TableB.fk
> >
> > AND TableA.id = TableC.fk
> >
> > However, this is not the result I need. What I need is a query with OR
> > instead of AND, like this:
> >
> > WHERE TableA.id = TableB.fk
> >
> > OR TableA.id = TableC.fk
> >
> > I haven't found any way to do this using criteria. Is it possible?
>
> I do not know a way to do this currently. But are you really sure your
> statement makes sense ? If one row in table B matches, then you get all
> rows of table C joined (or vice versa), and I cannot imagine this is
what
> you need. But I might be wrong.
>
> Thomas
>
> It does make sense. I rewrote the query by hand (changed AND to OR) and
> executed it in the MySql console and it gave the response I wanted.
>
> The situation is kind of like this:
> Table a is a category table, it only contains ids and names of
categories.
> Table B is a "products sold" table, and table C is a "products bought"
> table. All products belong to a certain category, and I want a query
that
> gives me all the categories that a certain user has sold/bought products
> from.
>
> /Ludwig
>
Ok, you have additional constraints on B and C; then this makes more
sense.
What you can do is to split this to 3 queries:
- Select the categories from A
- add the categories from B
- Do a select on the products table with the result.
The only performance disadvantage is that you have 3 queries.
Alternatively you could reformulate your query using subselects, which is
supported in Torque 3.3.
Thomas
Yes, the problem is always solveable, but it would be nice to be able to do
it in one criteria. I will use a temporary solution for now however.
/Ludwig
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
