By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,666 Members | 1,924 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,666 IT Pros & Developers. It's quick & easy.

left outer join issue?

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.