left outer join issue?

[Dev]

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>http://www.umpa-us.com
1-800-555-9665 ext 212

[Dev]

Re: left outer join issue?

At 02:28 PM 5/18/2004, Bruno Wolff III wrote:[color=blue]
>On Tue, May 18, 2004 at 14:10:04 -0400,
> Dev <dev@umpa-us.com> wrote:[color=green]
> >
> > 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?[/color]
>
>Use the ON clause syntax.[/color]

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>http://www.umpa-us.com
1-800-555-9665 ext 212

[Bruno Wolff III]

Re: left outer join issue?

On Tue, May 18, 2004 at 14:10:04 -0400,
Dev <dev@umpa-us.com> wrote:[color=blue]
>
> 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?[/color]

Use the ON clause syntax.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Bruno Wolff III]

Re: left outer join issue?

On Tue, May 18, 2004 at 14:26:18 -0400,
Dev <dev@umpa-us.com> wrote:[color=blue]
> At 02:28 PM 5/18/2004, Bruno Wolff III wrote:[color=green]
> >On Tue, May 18, 2004 at 14:10:04 -0400,
> > Dev <dev@umpa-us.com> wrote:[color=darkred]
> >>
> >> 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?[/color]
> >
> >Use the ON clause syntax.[/color]
>
> 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[/color]

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|| '%') )

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Keary Suska]

Re: left outer join issue?

on 5/18/04 12:26 PM, dev@umpa-us.com purportedly said:
[color=blue]
> At 02:28 PM 5/18/2004, Bruno Wolff III wrote:[color=green]
>> On Tue, May 18, 2004 at 14:10:04 -0400,
>> Dev <dev@umpa-us.com> wrote:[color=darkred]
>>>
>>> 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?[/color]
>>
>> Use the ON clause syntax.[/color]
>
> 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[/color]

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"


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Mike Rylander]

Re: left outer join issue?

On Tuesday 18 May 2004 02:26 pm, Dev wrote:[color=blue]
> At 02:28 PM 5/18/2004, Bruno Wolff III wrote:[color=green]
> >On Tue, May 18, 2004 at 14:10:04 -0400,
> >
> > Dev <dev@umpa-us.com> wrote:[color=darkred]
> > > 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?[/color]
> >
> >Use the ON clause syntax.[/color]
>
> Here is what it actually looks like
> LEFT OUTER JOIN table4 AS d ON (a.field1 ilike "%d.field2% ") )[/color]

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

-miker

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend