Re: How to arrange this query ???

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:35:09 CDT