469,270 Members | 1,026 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

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

Nov 23 '05 #1
5 2038
Dev
At 02:28 PM 5/18/2004, Bruno Wolff III wrote:
On Tue, May 18, 2004 at 14:10:04 -0400,
Dev <de*@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>http://www.umpa-us.com
1-800-555-9665 ext 212

Nov 23 '05 #2
On Tue, May 18, 2004 at 14:10:04 -0400,
Dev <de*@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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3
On Tue, May 18, 2004 at 14:26:18 -0400,
Dev <de*@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 <de*@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|| '%') )

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

http://archives.postgresql.org

Nov 23 '05 #4
on 5/18/04 12:26 PM, de*@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 <de*@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"
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #5
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 <de*@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

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

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ian Boyd | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.