Re: Help with a query

On Friday 25 Apr 2003 8:41 am, jose antonio leo wrote:
> aec_cua_man:
> ac1-ac2-ac3
> 1-1-1
> 1-1-2
> 1-1-4
> 1-2-2
> ....
>
> aecoc:
> ac1-ac2-ac3-description
> 1-0-0-des1
> 1-1-0-des2
> 1-1-1-des3
> 1-1-2-des4
> 1-1-3-des5
> 1-1-4-des6
> 1-1-5-des7
> 1-2-0-des8
> 1-2-1-des9
> 1-2-2-des10
> ....
>
> I need the record of the first table with the fields ac1, ac2 and ac3
> equals than second table but in addition the fiels of the second table with
> a nul in the fiels ac2 and ac3.
>
> the table result will be:
> 1-0-0-des1
> 1-1-0-des2
> 1-1-1-des3
> 1-1-2-des4
> 1-1-4-des6
> 1-2-0-des8
> 1-2-2-des10
>
> Could you help me?

Simplest method might be a union - do the join on the 3 fields and union it
with a simple select on aecoc where ac3=0 or ac2=0. Syntax is something like:

SELECT ac1,ac2,ac3,description FROM .... (join here)
UNION
SELECT ac1,ac2,ac3,description FROM aecoc... (grab zero descriptions here)

Note that the order of the result columns should be the same in both cases. If
you know there will be no zeros in aec_cua_man then you might want to use
UNION ALL.

See the User's guide Ch4 or the SQL SELECT reference for some details.

--
Richard Huxton