You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Jonathan Halland <jo...@spiraleye.com> on 2007/10/17 18:03:37 UTC
Left Join on multiple clauses
Hi,
I'm new to this list, but I've been struggling for quite a while to get the
following right.
I need to do a left join on two clauses e.g.
LEFT JOIN campaign_handled_objects on
accounts.ACCOUNT_ID=campaign_handled_objects.OBJECT_ID AND
campaign_handled_objects.CAMPAIGN_ID=16
So far no such luck.
Any Help would be immensely appreciated
Regards
Jonathan
Re: Left Join on multiple clauses
Posted by Thomas Vandahl <tv...@apache.org>.
Jonathan Halland wrote:
> My final aim for the query is to get the ones that do not join. I can used a
> nested "not in" subselect, but it is just incredibly slow when doing the
> select. Any suggestions on how else to do it?
Like this:
criteria.addJoin(AccountPeer.ACCOUNT_ID,
CampaignHandledObjectsPeer.OBJECT_ID, Criteria.LEFT_JOIN);
criteria.add(CampaignHandledObjectsPeer.OBJECT_ID, 0,
Criteria.IS_NULL);
Bye, Thomas.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Left Join on multiple clauses
Posted by Jonathan Halland <jo...@spiraleye.com>.
My final aim for the query is to get the ones that do not join. I can used a
nested "not in" subselect, but it is just incredibly slow when doing the
select. Any suggestions on how else to do it?
Regards
Jonathan
-----Original Message-----
From: Thomas Fischer [mailto:fischer@seitenbau.net]
Sent: 18 October 2007 01:40 PM
To: Apache Torque Users List
Subject: RE: Left Join on multiple clauses
Torque can only create joins using one join statement per join; and the
join operator must be the "equals" operator. If you need something else,
you would have to assemble at least parts of the sql statement manually.
Having said that, I'm not sure whether the statement in your last mail is
correct, but I have not tested it. Make sure you have tested it.
As a last resort, you can always use two selects, one which gets the
records which do have an associated record in the joined table and one
which gets the others.
Thomas
"Jonathan Halland" <jo...@spiraleye.com> schrieb am 18.10.2007 13:04:21:
> If I move the CAMPAIGN_ID=16 to the where clause I will essentially have
the
> same as a inner join, I however need to access the records that do not
> necessarily have a matching record in the right side table.
>
> Regards
> JOnathan
>
> -----Original Message-----
> From: Manaster, Carl [mailto:CarlM@ACTIVX.com]
> Sent: 17 October 2007 06:09 PM
> To: Apache Torque Users List
> Subject: RE: Left Join on multiple clauses
>
> Hi, Jonathan,
>
> As I see it, "joining" to a constant doesn't really make sense, although
> it might work in some or all cases, might even be perfectly valid
> syntactically. I would move the CAMPAIGN_ID=16 test to the WHERE
> clause.
>
> Peace,
> --Carl
>
> -----Original Message-----
> From: Jonathan Halland [mailto:jonathan@spiraleye.com]
> Sent: Wednesday, October 17, 2007 9:04 AM
> To: torque-user@db.apache.org
> Subject: Left Join on multiple clauses
>
>
>
>
> Hi,
>
>
>
> I'm new to this list, but I've been struggling for quite a while
> to get the following right.
>
>
>
> I need to do a left join on two clauses e.g.
>
>
>
> LEFT JOIN campaign_handled_objects on
> accounts.ACCOUNT_ID=campaign_handled_objects.OBJECT_ID AND
> campaign_handled_objects.CAMPAIGN_ID=16
>
>
>
> So far no such luck.
>
>
>
> Any Help would be immensely appreciated
>
>
>
> Regards
>
> Jonathan
>
>
>
>
>
> ---------------------------------------------------------------------
> 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
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Left Join on multiple clauses
Posted by Thomas Vandahl <tv...@apache.org>.
Jonathan Halland wrote:
> If I move the CAMPAIGN_ID=16 to the where clause I will essentially have the
> same as a inner join, I however need to access the records that do not
> necessarily have a matching record in the right side table.
If I understand you correctly, you are trying to get all records from
table "account" which may or may not have a matching record in table
"campaign_handled_objects", right?
> LEFT JOIN campaign_handled_objects on
> accounts.ACCOUNT_ID=campaign_handled_objects.OBJECT_ID AND
> campaign_handled_objects.CAMPAIGN_ID=16
What you do here is to query all records from table "account" which *do*
have a matching record in table "campaign_handled_objects", namely the
ones that have CAMPAIGN_ID=16. This is in fact an inner join, because
you specified a record field from the right table to be *not null*,
which means that the record must exist. The correct SQL for the above
would read like
LEFT JOIN campaign_handled_objects on
accounts.ACCOUNT_ID=campaign_handled_objects.OBJECT_ID
WHERE (campaign_handled_objects.CAMPAIGN_ID=16 OR
campaign_handled_objects.CAMPAIGN_ID=16 IS NULL)
Bye, Thomas.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Left Join on multiple clauses
Posted by Thomas Fischer <fi...@seitenbau.net>.
Torque can only create joins using one join statement per join; and the
join operator must be the "equals" operator. If you need something else,
you would have to assemble at least parts of the sql statement manually.
Having said that, I'm not sure whether the statement in your last mail is
correct, but I have not tested it. Make sure you have tested it.
As a last resort, you can always use two selects, one which gets the
records which do have an associated record in the joined table and one
which gets the others.
Thomas
"Jonathan Halland" <jo...@spiraleye.com> schrieb am 18.10.2007 13:04:21:
> If I move the CAMPAIGN_ID=16 to the where clause I will essentially have
the
> same as a inner join, I however need to access the records that do not
> necessarily have a matching record in the right side table.
>
> Regards
> JOnathan
>
> -----Original Message-----
> From: Manaster, Carl [mailto:CarlM@ACTIVX.com]
> Sent: 17 October 2007 06:09 PM
> To: Apache Torque Users List
> Subject: RE: Left Join on multiple clauses
>
> Hi, Jonathan,
>
> As I see it, "joining" to a constant doesn't really make sense, although
> it might work in some or all cases, might even be perfectly valid
> syntactically. I would move the CAMPAIGN_ID=16 test to the WHERE
> clause.
>
> Peace,
> --Carl
>
> -----Original Message-----
> From: Jonathan Halland [mailto:jonathan@spiraleye.com]
> Sent: Wednesday, October 17, 2007 9:04 AM
> To: torque-user@db.apache.org
> Subject: Left Join on multiple clauses
>
>
>
>
> Hi,
>
>
>
> I'm new to this list, but I've been struggling for quite a while
> to get the following right.
>
>
>
> I need to do a left join on two clauses e.g.
>
>
>
> LEFT JOIN campaign_handled_objects on
> accounts.ACCOUNT_ID=campaign_handled_objects.OBJECT_ID AND
> campaign_handled_objects.CAMPAIGN_ID=16
>
>
>
> So far no such luck.
>
>
>
> Any Help would be immensely appreciated
>
>
>
> Regards
>
> Jonathan
>
>
>
>
>
> ---------------------------------------------------------------------
> 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
RE: Left Join on multiple clauses
Posted by Jonathan Halland <jo...@spiraleye.com>.
If I move the CAMPAIGN_ID=16 to the where clause I will essentially have the
same as a inner join, I however need to access the records that do not
necessarily have a matching record in the right side table.
Regards
JOnathan
-----Original Message-----
From: Manaster, Carl [mailto:CarlM@ACTIVX.com]
Sent: 17 October 2007 06:09 PM
To: Apache Torque Users List
Subject: RE: Left Join on multiple clauses
Hi, Jonathan,
As I see it, "joining" to a constant doesn't really make sense, although
it might work in some or all cases, might even be perfectly valid
syntactically. I would move the CAMPAIGN_ID=16 test to the WHERE
clause.
Peace,
--Carl
-----Original Message-----
From: Jonathan Halland [mailto:jonathan@spiraleye.com]
Sent: Wednesday, October 17, 2007 9:04 AM
To: torque-user@db.apache.org
Subject: Left Join on multiple clauses
Hi,
I'm new to this list, but I've been struggling for quite a while
to get the following right.
I need to do a left join on two clauses e.g.
LEFT JOIN campaign_handled_objects on
accounts.ACCOUNT_ID=campaign_handled_objects.OBJECT_ID AND
campaign_handled_objects.CAMPAIGN_ID=16
So far no such luck.
Any Help would be immensely appreciated
Regards
Jonathan
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Left Join on multiple clauses
Posted by "Manaster, Carl" <Ca...@ACTIVX.com>.
Hi, Jonathan,
As I see it, "joining" to a constant doesn't really make sense, although
it might work in some or all cases, might even be perfectly valid
syntactically. I would move the CAMPAIGN_ID=16 test to the WHERE
clause.
Peace,
--Carl
-----Original Message-----
From: Jonathan Halland [mailto:jonathan@spiraleye.com]
Sent: Wednesday, October 17, 2007 9:04 AM
To: torque-user@db.apache.org
Subject: Left Join on multiple clauses
Hi,
I'm new to this list, but I've been struggling for quite a while
to get the following right.
I need to do a left join on two clauses e.g.
LEFT JOIN campaign_handled_objects on
accounts.ACCOUNT_ID=campaign_handled_objects.OBJECT_ID AND
campaign_handled_objects.CAMPAIGN_ID=16
So far no such luck.
Any Help would be immensely appreciated
Regards
Jonathan