Re: How to arrange this query ???

Many thanks,
I'll try and make you know as soon as possible. Thanks

Antonio D'Ottavio
www.etantonio.it/en

Charles Hooper ha scritto:

> etantonio_at_libero.it wrote:
> > The tables in the database are the following :
> >
> > T1
> > cod_dest
> > descr_dest
> >
> >
> > T2
> > cod_dest
> >
> >
> > in my select I need 3 columns
> > T1.COD-dest , T1.DESCR-dest , 0 or 1 depending if T1.cod_dest is also
> > present in T2.cod_dest.
> >
> > Can you help me to arrange such query ???
> > many thanks
> >
> >
> > Antonio D'Ottavio
> > www.etantonio.it/en
>
> Sybrand Bakker's outer join suggestion is just what is needed.
>
> Here is an example. Assume that you have two tables PART (your table
> T1) and NCMR (your table T2) - which could have 0, 1, 2, or more than
> 100 entries for each row in the PART table. PART.ID (your T1.COD_DEST
> column) is set up in an outer join with NCMR.PART_ID (your T2.COD_DEST
> column), and PART.DESCRIPTION (your T1.DESCR_DEST) is also included.
> Because there could be 0 or more than 100 matches in the NCMR (your T2)
> table, it is necessary to group by the other columns being retrieved
> and to retrieve the COUNT of the matching rows from the NCMR (your T2)
> table. The COUNT is transformed into 0 or 1 with the SIGN function (-1
> if negative, 0 if 0, 1 if positive).
>
> SELECT
> P.ID,
> P.DESCRIPTION,
> SIGN(COUNT(N.PART_ID)) IS_PRESENT
> FROM
> PART P,
> NCMR N
> WHERE
> P.ID=N.PART_ID(+)
> GROUP BY
> P.ID,
> P.DESCRIPTION;
>
> Use the above example to work out your SELECT statement.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Mon Jul 31 2006 - 05:41:58 CDT