Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Users List" <torque-user@db.apache.org>
Received-SPF: neutral (athena.apache.org: local policy)
From: "Ludwig Magnusson" <ludwig@itcatapult.com>
To: "'Apache Torque Users List'" <torque-user@db.apache.org>
References: <002101c99b37$3cdc31e0$b69495a0$@com>
 <OF93F675E9.2B4FDFE3-ONC125756D.0049837E-C125756D.0049DD72@seitenbau.net>
 <002a01c99b3b$fb1876b0$f1496410$@com> <49AC4442.6060309@gmx.org>
In-Reply-To: <49AC4442.6060309@gmx.org>
Subject: RE: Multiple addJoin with OR
Date: Mon, 9 Mar 2009 12:06:42 +0100
Message-ID: <001401c9a0a7$2116ba50$63442ef0$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AcmbzfUh1wkA7LxPTuS/dUiHu+vB1QE2F46g
Content-Language: sv

Hello again!
Sorry I haven't replied for a while.
I solved the problem however by adding a custom part to the criteria:

criteria.add(CategoryPeer.ID, (Object) String.format("(%s=3D%s OR =
%s=3D%s)",=20
CategoryPeer.ID, SoldProductPeer.CATEGORY_ID,=20
CategoryPeer.ID, BoughtProductPeer.CATEGORY_ID),=20
SqlEnum.CUSTOM);

This gave me the result I wanted. Not perfect, but it works.
/Ludwig

-----Original Message-----
From: Marc Kannegie=DFer [mailto:macmac@gmx.org]=20
Sent: den 2 mars 2009 21:41
To: Apache Torque Users List
Subject: Re: Multiple addJoin with OR

Ludwig Magnusson wrote:
> 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.
>=20
> 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.
>=20
> /Ludwig

I still don't think it makes sense using joins here:

When joining tables you usually "combine" rows of multiple tables
(interested in results from both tables). What you're trying to do is
selecting only categories (from table a) and you don't seem to be
interested in the corresponding entries in tables b and c.
It really seems a subquery is the right thing to use here:

SELECT * FROM category a WHERE EXISTS ( SELECT * FROM bought b where
a.id =3D  b.fk ) OR EXISTS ( SELECT * FROM sold c where a.id =3D c.fk);


Im also not sure if your join works if you'd use the ON-Clause that is
usually ment for join-conditions instead of WHERE:

SELECT * FROM a JOIN b ON a.id =3D b.fk OR JOIN c ON a.id =3D c.id
OR is not allowed here ---------------^

If you execute the statement you describe the DBMS (IMHO!) actually
produces a Cartesian product and applies the WHERE-Conditions =
"afterwards":
SELECT Category.* from a, b, c WHERE a.id =3D b.fk OR a.id =3D c.fk

This means (no DBMS-internal optimization assumed) the DBMS first
"combines" each row of a with all rows of b and then combines all of
these rows to all rows of c. This means you'll have a "virtual table" of
rows_a * rows_b * rows_c size which get reduced by the WHERE-Clause
afterwards. No real "join" as you try to approach, i think ;)

See for example http://dev.mysql.com/doc/refman/5.0/en/join.html for
more information about JOINs

Just my 2ct.

Greetings,

Marc


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org