left outer join issue?

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

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.

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

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

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"