openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From José Luis Cetina <maxtorz...@gmail.com>
Subject Re: Support for LEFT OUTER JOIN ON
Date Fri, 12 Oct 2012 14:29:25 GMT
coalesce is a sql ansi function. With Oracle you can use it with Coalesce
or whit nvl function, in mysql you can use Coalesce or ifnull function, the
difference is that Coalesce is "standard", and the other is db specific.

Example:
Select * FROM x,y WHERE coalesce x.id = coalesce(y.id_fk, x.id).

Here if y.id_fk is null then the value will be x.id (the second parameter
specify in Coalesce function), you can use this if you know if your value
can be null and you want use other value.
El 12/10/2012 07:30, "Maxim Solodovnik" <solomax666@gmail.com> escribió:

> Thanks for the quick reply :),
>
> I'm sure how coalesce can help me.
> Can you write a short example?
>
> Thanks in advance :)
>
> On Fri, Oct 12, 2012 at 7:13 PM, José Luis Cetina <maxtorzito@gmail.com
> >wrote:
>
> > Why you don't use the coalesce function?
> > El 12/10/2012 04:05, "Maxim Solodovnik" <solomax666@gmail.com> escribió:
> >
> > > Hello,
> > >
> > > does anybody knows if is there any support for "LEFT OUTER JOIN ON"
> > > construction (Or maybe any plan to support it)?
> > >
> > > I would like to perform join of 2 tables and set values to NULL in case
> > > there are no values.
> > >
> > > In MySQL it can be written as follows:
> > >
> > > SELECT t0.id, t1.id
> > > FROM
> > >     fieldvalues t0
> > >     LEFT OUTER JOIN fieldlanguagesvalues t1 ON (t0.id =
> > t1.fieldvalues_id
> > > AND t1.language_id = 34)
> > > WHERE t0.deleted = 0 LIMIT 50;
> > >
> > > This query will select all fieldvalues and will add
> > > existent fieldlanguagesvalues or NULL.
> > >
> > >
> > > Thanks in advance
> > >
> > > --
> > > WBR
> > > Maxim aka solomax
> > >
> >
>
>
>
> --
> WBR
> Maxim aka solomax
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message