Postgres Pro
Downloads
Home   >   mailing lists

Thread: left outer join issue?

left outer join issue?

From
Dev
Date:
Hello all,

I need to do a left out join where a.field1 ilike %b.field2% 

But I can not figure out the exact syntax to using the ilike in the join?


UMPA Brian C. Doyle
Director, Internet Services
United Merchant Processing Association
http://www.umpa-us.com
1-800-555-9665 ext 212

Re: left outer join issue?

From
Bruno Wolff III
Date:
On Tue, May 18, 2004 at 14:10:04 -0400,
  Dev <dev@umpa-us.com> wrote:
>
> Hello all,
>
> I need to do a left out join where a.field1 ilike %b.field2%
>
> But I can not figure out the exact syntax to using the ilike in the join?

Use the ON clause syntax.

Re: left outer join issue?

From
Dev
Date:
At 02:28 PM 5/18/2004, Bruno Wolff III wrote:
On Tue, May 18, 2004 at 14:10:04 -0400,
  Dev <dev@umpa-us.com> wrote:
>
> Hello all,
>
> I need to do a left out join where a.field1 ilike %b.field2%
>
> But I can not figure out the exact syntax to using the ilike in the join?

Use the ON clause syntax.

Here is what it actually looks like
 LEFT OUTER JOIN table4 AS d ON (a.field1 ilike "%d.field2%") )

a is setup for table1 and it is reference in a few other left outer joins. 

Those "other" outer joins work fine with out this new part


UMPA Brian C. Doyle
Director, Internet Services
United Merchant Processing Association
http://www.umpa-us.com
1-800-555-9665 ext 212

Re: left outer join issue?

From
Bruno Wolff III
Date:
On Tue, May 18, 2004 at 14:26:18 -0400,
  Dev <dev@umpa-us.com> wrote:
> At 02:28 PM 5/18/2004, Bruno Wolff III wrote:
> >On Tue, May 18, 2004 at 14:10:04 -0400,
> >  Dev <dev@umpa-us.com> wrote:
> >>
> >> Hello all,
> >>
> >> I need to do a left out join where a.field1 ilike %b.field2%
> >>
> >> But I can not figure out the exact syntax to using the ilike in the join?
> >
> >Use the ON clause syntax.
>
> Here is what it actually looks like
>  LEFT OUTER JOIN table4 AS d ON (a.field1 ilike "%d.field2%") )
>
> a is setup for table1 and it is reference in a few other left outer joins.
>
> Those "other" outer joins work fine with out this new part

Maybe this isn't really an left join syntax question as much as
an expression syntax question. Maybe you want:
LEFT OUTER JOIN table4 AS d ON (a.field1 ilike ('%' || d.field2|| '%') )

Re: left outer join issue?

From
Mike Rylander
Date:
On Tuesday 18 May 2004 02:26 pm, Dev wrote:
> At 02:28 PM 5/18/2004, Bruno Wolff III wrote:
> >On Tue, May 18, 2004 at 14:10:04 -0400,
> >
> >   Dev <dev@umpa-us.com> wrote:
> > > Hello all,
> > >
> > > I need to do a left out join where a.field1 ilike %b.field2%
> > >
> > > But I can not figure out the exact syntax to using the ilike in the
> > > join?
> >
> >Use the ON clause syntax.
>
> Here is what it actually looks like
>   LEFT OUTER JOIN table4 AS d ON (a.field1 ilike "%d.field2%") )

Use
        LEFT OUTER JOIN table4 AS d ON (a.field1 ILIKE '%' || d.field2 || '%')

-miker

Re: left outer join issue?

From
Keary Suska
Date:
on 5/18/04 12:26 PM, dev@umpa-us.com purportedly said:

> At 02:28 PM 5/18/2004, Bruno Wolff III wrote:
>> On Tue, May 18, 2004 at 14:10:04 -0400,
>>   Dev <dev@umpa-us.com> wrote:
>>>
>>> Hello all,
>>>
>>> I need to do a left out join where a.field1 ilike %b.field2%
>>>
>>> But I can not figure out the exact syntax to using the ilike in the join?
>>
>> Use the ON clause syntax.
>
> Here is what it actually looks like
> LEFT OUTER JOIN table4 AS d ON (a.field1 ilike "%d.field2%") )
>
> a is setup for table1 and it is reference in a few other left outer joins.
>
> Those "other" outer joins work fine with out this new part

Your field reference is incorrect. Use concatenation:

    ON (a.field1 ilike '%'||d.field2||'%') )

Remember in Postgres, double quotes are only used to delimit identifiers.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"