Oninit cdi / Archive
Talk to an expert →

*** Unwanted OUTER join, please help ***

Posted in 2000

Topics: SQL Development & Query Writing

spammers_suck@my-deja.com — — source: Informix-list mailing list archive (1991-1998)

Forgive the reposting. I need an answer and want to change the subject line to attract more guru attention :) Its a strange thing. I want do : FROM a, outer b, outer c, (d, outer e) Informix insists that I do : FROM a, outer b, outer c, outer (d, outer e) But I don't want d to be an outer join with a ! It returns unwanted "extra" rows. I want it to be a "regular" join. Any ideas? Why this apparent limitation? Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to spammers_suck@my-deja.com
dyrson@my-deja.com — — source: Informix-list mailing list archive (1991-1998)

My first advice is to have a look at the Informix Guide to SQL - Tutorial. It has a complete explanation of outer joins. The first table in your join is the dominant one. All its rows will be retrieved (since they satisfy the where clause). You can have only one dominant table in a outer join statement. So all the other tables (or group of tables) must have an outer clause. Maybe your dominant table is table d. Maybe you want to do this from a, outer b, outer c ... or from a, outer (b, c, ...) or from a, outer (b, outer c) Dyrson In article <8iap20$s4i$1@nnrp1.deja.com>, spammers_suck@my-deja.com wrote: > Forgive the reposting. I need an answer and want to change the subject > line to attract more guru attention :) > > Its a strange thing. > > I want do : > > FROM a, outer b, outer c, (d, outer e) > > Informix insists that I do : > > FROM a, outer b, outer c, outer (d, outer e) > > But I don't want d to be an outer join with a ! It returns unwanted > "extra" rows. I want it to be a "regular" join. > > Any ideas? > > Why this apparent limitation? > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to dyrson@my-deja.com
spammers_suck@my-deja.com — — source: Informix-list mailing list archive (1991-1998)

Hi I'm fairly conversant with outer joins. My example is very specific about the "unwanted" outer problem. Any other thoughts? In article <8iar9k$tvc$1@nnrp1.deja.com>, dyrson@my-deja.com wrote: > My first advice is to have a look at the Informix Guide to SQL - > Tutorial. It has a complete explanation of outer joins. > The first table in your join is the dominant one. All its rows will be > retrieved (since they satisfy the where clause). You can have only one > dominant table in a outer join statement. So all the other tables (or > group of tables) must have an outer clause. Maybe your dominant table is > table d. Maybe you want to do this > > from a, outer b, outer c ... or > > from a, outer (b, c, ...) or > > from a, outer (b, outer c) > > Dyrson > > In article <8iap20$s4i$1@nnrp1.deja.com>, > spammers_suck@my-deja.com wrote: > > Forgive the reposting. I need an answer and want to change the > subject > > line to attract more guru attention :) > > > > Its a strange thing. > > > > I want do : > > > > FROM a, outer b, outer c, (d, outer e) > > > > Informix insists that I do : > > > > FROM a, outer b, outer c, outer (d, outer e) > > > > But I don't want d to be an outer join with a ! It returns unwanted > > "extra" rows. I want it to be a "regular" join. > > > > Any ideas? > > > > Why this apparent limitation? > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to spammers_suck@my-deja.com
Carlson@WHSmith — — source: Informix-list mailing list archive (1991-1998)

spammers_suck@my-deja.com wrote: > > Forgive the reposting. I need an answer and want to change the subject > line to attract more guru attention :) > > Its a strange thing. > > I want do : > > FROM a, outer b, outer c, (d, outer e) > > Informix insists that I do : > > FROM a, outer b, outer c, outer (d, outer e) > > But I don't want d to be an outer join with a ! It returns unwanted > "extra" rows. I want it to be a "regular" join. > > Any ideas? > > Why this apparent limitation? > > Sent via Deja.com http://www.deja.com/ > Before you buy. Could you post a copy of the SQL in question? Some times I've ended up with double-sided joins only to find that there was an issue with the joins in the WHERE clause. -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

↪ replying to spammers_suck@my-deja.com
Art S. Kagel — — source: Informix-list mailing list archive (1991-1998)

Have you tried the following, a similar query I use gives desired results: SELECT ... FROM a, d, outer b, outer c, outer e WHERE a.key = d.key AND a.attrib = b.attrib AND a.attrib2 = c.attrib2 AND d.key = e.key.... The key is in the join conditions in the where clause. Nesting for outer joins is ONLY used to join to a table which is itself OUTER joined and so to indicated the subservient nature of the nested join it must be in parenthesis. The syntax you tried is being disallowed because the parenthesis is ONLY for a nested OUTER join. Art S. Kagel spammers_suck@my-deja.com wrote: > > Hi I'm fairly conversant with outer joins. > > My example is very specific about the "unwanted" outer problem. > > Any other thoughts? > > In article <8iar9k$tvc$1@nnrp1.deja.com>, > dyrson@my-deja.com wrote: > > My first advice is to have a look at the Informix Guide to SQL - > > Tutorial. It has a complete explanation of outer joins. > > The first table in your join is the dominant one. All its rows will be > > retrieved (since they satisfy the where clause). You can have only one > > dominant table in a outer join statement. So all the other tables (or > > group of tables) must have an outer clause. Maybe your dominant table > is > > table d. Maybe you want to do this > > > > from a, outer b, outer c ... or > > > > from a, outer (b, c, ...) or > > > > from a, outer (b, outer c) > > > > Dyrson > > > > In article <8iap20$s4i$1@nnrp1.deja.com>, > > spammers_suck@my-deja.com wrote: > > > Forgive the reposting. I need an answer and want to change the > > subject > > > line to attract more guru attention :) > > > > > > Its a strange thing. > > > > > > I want do : > > > > > > FROM a, outer b, outer c, (d, outer e) > > > > > > Informix insists that I do : > > > > > > FROM a, outer b, outer c, outer (d, outer e) > > > > > > But I don't want d to be an outer join with a ! It returns > unwanted > > > "extra" rows. I want it to be a "regular" join. > > > > > > Any ideas? > > > > > > Why this apparent limitation? > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.