Didn't work - probably because the 2 fields are in the same table.

Someone else here suggested using union on the 2 fields, which works.

SELECT tbl.A from tbl UNION select tbl.B from tbl

Thanks,
Mike


----- Original Message -----
From: Donna Collins <dcollins_92117@y...>
To: sql language <sql_language@p...>
Sent: Tuesday, May 01, 2001 4:47 PM
Subject: [sql_language] Re: using distinct


> Select distinct A.*, B.*
> from A inner join B on A_indexfieldname=B_indexfieldname
>
> When the dbf's are joined, the distinct command evaluates data based on
> the joined condition.
>
> Thanks,
> Donna
>
> > A table has a key field, and fields A & B.  Some records have the same
> > values for A & B.  For example:
> >
> > A                        B
> > 1                         2
> > 1                         1
> > 3                         2
> > 2                         1
> >
> > I want to set up a query which only returns one record for each value.
I
> > tried:
> >
> > SELECT DISTINCT tbl.A, tbl.B
> > FROM tbl
> > WHERE tbl.A<>"" or tbl.B<>"";
> >
> > Problem is that this returns some identical recordsets:
> >
> > 1
> > 3
> > 2
> > 2
> > 1
> >
> > The SQL statement evaluates for distinct on each field, not on both, so
I
> > get duplicates.  If I used:
> > WHERE tbl.A<>"" and tbl.B<>""; the recordset would be missing "3".
> >
> > Any suggestions?  Currently I'm using Access and ASP, but will move to
> SQL
> > Server at some point.
> >
> > Thanks in advance,
> >
> > Mike
> >
>
$subst('Email.Unsub')
>