Use distinct on one column but want 2 columns of data returned   

Thank you in advance for any and all assistance.  I am trying to do a 
comparison of 2 tables and I am expecting the result to be what ever is not 
in one of the tables.  The table I need the result from has 2 columns of data 
that I want returned based on col1 being distinct because I know it has 
duplicates in it.  

Ex.  TableA: Col1, Col2  I want my result to have both columns of data based 
on Col1 being distinct

TableB: Col3

I compare TableA.Col1 to TableB.Col3 whatever is NOT in TableB but is in 
TableA should be in the result.  

I have tried this query:

Select distinct TableA.Col1,TableA.Col2 
From TableA
Left Join TableB ON Table.Col1 = TableB.Col3
Where TableB is Null

The result I get has duplicates.

I have also tried this query:

Select distinct TableA.Col1,TableA.Col2 
From TableA
Where Not Exists
(
Select * 
From TableB
Where TableA.Col1 = TableB.Col3
)

Again the result contains duplicate data.  Can anyone clarify forme what I 
am doing wrong?

Thanks,
Erik

date: Thu, 1 Nov 2007 14:36:02 -0700   author:   elittle

Re: Use distinct on one column but want 2 columns of data returned   

Eric,

So, if your table may has rows like below, what do you want as a result?
    Col1, Col2
    1, 15
    1, 23
If you want only one instance of Col1 = 1, then you have to choose what Col2 
will be, MIN, MAX, SUM, AVG, and so forth. By recasting as a group by 
instead of a distinct you could do this:

Select TableA.Col1, Min(TableA.Col2) AS MinCol2,
            Max(TableA.Col2) AS MaxCol2, Count(*) AS DuplicateCol1Count
From TableA
Where Not Exists
(
Select *
From TableB
Where TableA.Col1 = TableB.Col3
)
Group by TableA.Col1

RLF


"elittle"  wrote in message 
news:7B627CB0-8AF6-4A03-802E-06F21DDECE93@microsoft.com...
> Thank you in advance for any and all assistance.  I am trying to do a
> comparison of 2 tables and I am expecting the result to be what ever is 
> not
> in one of the tables.  The table I need the result from has 2 columns of 
> data
> that I want returned based on col1 being distinct because I know it has
> duplicates in it.
>
> Ex.  TableA: Col1, Col2  I want my result to have both columns of data 
> based
> on Col1 being distinct
>
> TableB: Col3
>
> I compare TableA.Col1 to TableB.Col3 whatever is NOT in TableB but is in
> TableA should be in the result.
>
> I have tried this query:
>
> Select distinct TableA.Col1,TableA.Col2
> From TableA
> Left Join TableB ON Table.Col1 = TableB.Col3
> Where TableB is Null
>
> The result I get has duplicates.
>
> I have also tried this query:
>
> Select distinct TableA.Col1,TableA.Col2
> From TableA
> Where Not Exists
> (
> Select *
> From TableB
> Where TableA.Col1 = TableB.Col3
> )
>
> Again the result contains duplicate data.  Can anyone clarify forme what I
> am doing wrong?
>
> Thanks,
> Erik

date: Fri, 2 Nov 2007 14:02:42 -0400   author:   Russell Fields