[RESOLVED] SQL Distinct columns

[scsfdev]

Hi all,
I want to know is there any way possible for me to select let's say 4 distinct columns + 1 not distinct column in one sql sentence?

I have following:
-UserRealName, UserEmail, UserCompany in tbl_UserMaster
-UserID, UserName in tbl_User
-UserID, DonatedFee, RecDate in tbl_Donate.

I want to get UserRealname, UserEmail, UserName, DonatedFee, RecDate.

Here is what I tried:

SELECT DISTINCT UM.UserName AS Name, UM.UserEmail AS UserEmail, UM.UserCompany AS UserCompany, 
ISNULL(U.UserName,'') AS UserName, ISNULL(D.DonatedFee,0) AS DonatedAmount, D.RecDate AS RecordDate
FROM tbl_UserMaster AS UM RIGHT JOIN tbl_User AS U ON 
U.UserName = UPPER(LEFT(UM.UserEmail,CHARINDEX('@',UM.UserEmail)-1)) LEFT JOIN tbl_Donate AS D 
ON D.UserID=U.UserID WHERE U.UserID NOT IN(SELECT DISTINCT UserID FROM tbl_Donate WHERE 
YEAR(RecDate) BETWEEN 2009 AND 2009 AND MONTH(RecDate) BETWEEN 5 AND 5 AND DAY(RecDate)  BETWEEN 8 AND 8) 
group by UM.UserName, UM.UserEmail, UM.UserCompany, U.UserName, D.DonatedFee, D.RecDate

It will retrieve a list of records but the records is not in distinct. It returns duplicate records.

Is there any way to retrieve all distinct records neglect the date column but want to include in result list?

Thanks your help.

P.S: I'm not that good enough in SQL too. The above sql and table structure of mine may be ugly but please forgive me

[jmcilhinney]

Either a record is distinct or its not. If you specify DISTINCT then you will get no duplicate records. Unless you're using an aggregate function you cannot group together two records that have a different value in any column you are selecting. How can one record contain two values in one field?

[scsfdev]

Either a record is distinct or its not. If you specify DISTINCT then you will get no duplicate records. Unless you're using an aggregate function you cannot group together two records that have a different value in any column you are selecting. How can one record contain two values in one field?

Hi jmcihinney,

Thanks for quick reply.

First, sorry for my mistake, the last line Group by is not included in my original SQL. (that is my testing. I just searched some post and they suggested to use Max(recdate) and put group by at last. I tested and its still not working.)

And the other one is I quite not understand what you mean "How can one record contain two values in one field?"

In first table, it has distinct UserRealname, UserEmail but duplicate company name.
In Second table, it has distinct UserID, UserName.
In Last table, it has duplicate UserID, DonateFee but with distinct date. (cuz recdate is in date time format).

So, when I use above sql to get my records, it will also include duplicate data example:

RealName    Email                    Company  UserName  DonateFee  RecDate
Aung Aung  [email protected]  ABC         aung         100           2009-05-03 09:33:10.000
Tun Tun     [email protected]   ABC         tun2          400          2009-05-04 10:21:46.000
Aung Aung  [email protected]  ABC         aung         200           2009-05-04 15:02:32.000

Above is the sample data that will get from my SQL statement. It include duplicate records (same realname, email, company, username. I can ignore donatefee and recdate).
Is there any way to get only 1 row if realname, email, company, username is same neglect the donatefee and recdate value?
I even tried to put null as donatefee, null as recdate, but it still not working. still has the above problem.

My idea is I will get all data within a particular date range. of course data will be in distinct not duplicate.
As far as I tested, all is working ok before I left join with Donate table. out of idea??

thanks.

[jmcilhinney]

But they're NOT duplicate records. They are two different records because they do not contain the same data. If you were to omit one of those records, which would it be? You can't just arbitrarily drop one of them because then you won't have all the relevant data. You can't combine them because, as I said, one record can't contain two values in one field. How would you combine the two dates?

Either you want every donation or you don't. If you don't then why are you joining with the Donate table? If you do then you need the data from the other table(s) duplicated because, in the context of this result set, it constitutes different records.

[techgnome]

You're going to have to be a bit more specific then.... I don't see any duplicate records... each of the three records are different.... Now that we know what you are getting.... what is it that you WANT to get? (don't describe the data... SHOW us what you are looking for.)

-tg

[scsfdev]

But they're NOT duplicate records. They are two different records because they do not contain the same data. If you were to omit one of those records, which would it be? You can't just arbitrarily drop one of them because then you won't have all the relevant data. You can't combine them because, as I said, one record can't contain two values in one field. How would you combine the two dates?

Either you want every donation or you don't. If you don't then why are you joining with the Donate table? If you do then you need the data from the other table(s) duplicated because, in the context of this result set, it constitutes different records.

Hi jmcilhinney,

It seems I am stupid enough to said distinct
Yes, according to what you said, they are not duplicate. they are distinct.
I just want to get all register user information which is not in a particular date.
So, I need to get date from Donate table. (In another point of view, I don't need to display data from Donation table just need to check the date)

sorry to bother you jm. and u too tg

I think I got it. Just need to check whether all required data are correct.

here is what I did:

SELECT UM.UserName AS Name, UM.UserEmail AS UserEmail, 
UM.UserCompany AS UserCompany, ISNULL(U.UserName,'') AS UserName, 
NULL as DonatedAmount, NULL AS RecordDate
FROM tbl_UserMaster AS UM RIGHT JOIN tbl_User AS U 
ON U.UserName = UPPER(LEFT(UM.UserEmail,CHARINDEX('@',UM.UserEmail)-1))
WHERE U.UserID NOT IN 
(SELECT DISTINCT UserID FROM tbl_Donate WHERE 
YEAR(RecDate) BETWEEN 2009 AND 2009 AND MONTH(RecDate) BETWEEN 5 AND 5 AND 
DAY(RecDate) BETWEEN 8 AND 8)

Thanks again jm and tg