> > 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