Very Computer

Friends,

When I use this Query I get Duplicate Records even though I have used the
"Distinct" keyword.
Does Distinct key word work in Joins?

Any Help would be Appreciated.

SELECT
Distinct(dbo.QORAccounts.ID),dbo.QORAccounts.CompleteDate,dbo.QORAccounts.Co
mpany,dbo.QORAccounts.ContactID,dbo.QORAccounts.ExecID,dbo.QORAccounts.Statu
s,dbo.QORAccounts.RegistrationType, dbo.QORAccounts.Suspend,
dbo.QORAccounts.DateAdded, dbo.Organizations.Registered,
dbo.Organizations.DateRegistered FROM dbo.QORAccounts LEFT JOIN
dbo.Organizations ON dbo.QORAccounts.ContactID =
dbo.Organizations.ContactMemberID where
dbo.QORAccounts.RegistrationType<>'OA' And dbo.QORAccounts.ContentControlID
= 209 Order By dbo.Organizations.DateRegistered asc;

--

Regards,
Santosh

When you have a LEFT JOIN, there is a chance for NULLs to be returned in the
resultset.
Hence DISTINCT on NULLs will not give you the results you want.

Hope it helps
Thanks
Anith

Quote:> Friends,

> When I use this Query I get Duplicate Records even though I have used the
> "Distinct" keyword.
> Does Distinct key word work in Joins?

> Any Help would be Appreciated.

> SELECT

Distinct(dbo.QORAccounts.ID),dbo.QORAccounts.CompleteDate,dbo.QORAccounts.Co
mpany,dbo.QORAccounts.ContactID,dbo.QORAccounts.ExecID,dbo.QORAccounts.Statu

Quote:> s,dbo.QORAccounts.RegistrationType, dbo.QORAccounts.Suspend,
> dbo.QORAccounts.DateAdded, dbo.Organizations.Registered,
> dbo.Organizations.DateRegistered FROM dbo.QORAccounts LEFT JOIN
> dbo.Organizations ON dbo.QORAccounts.ContactID =
> dbo.Organizations.ContactMemberID where
> dbo.QORAccounts.RegistrationType<>'OA' And

dbo.QORAccounts.ContentControlID

Quote:> = 209 Order By dbo.Organizations.DateRegistered asc;

> --

> Regards,
> Santosh

Use a subquery

SELECT
 Distinct QORAccounts.ID
FROM
(select QORAccounts.ID,dbo.QORAccounts.CompleteDate,dbo.QORAccounts.Co

mpany,dbo.QORAccounts.ContactID,dbo.QORAccounts.ExecID,dbo.QORAccounts.Statu
s,dbo.QORAccounts.RegistrationType, dbo.QORAccounts.Suspend,
 dbo.QORAccounts.DateAdded, dbo.Organizations.Registered,
 dbo.Organizations.DateRegistered FROM dbo.QORAccounts LEFT JOIN
 dbo.Organizations ON dbo.QORAccounts.ContactID =
 dbo.Organizations.ContactMemberID where
 dbo.QORAccounts.RegistrationType<>'OA' And dbo.QORAccounts.ContentControlID
 = 209 Order By dbo.Organizations.DateRegistered asc)

James M

Quote:> Friends,

> When I use this Query I get Duplicate Records even though I have used the
> "Distinct" keyword.
> Does Distinct key word work in Joins?

> Any Help would be Appreciated.

> SELECT

Distinct(dbo.QORAccounts.ID),dbo.QORAccounts.CompleteDate,dbo.QORAccounts.Co
mpany,dbo.QORAccounts.ContactID,dbo.QORAccounts.ExecID,dbo.QORAccounts.Statu

Quote:> s,dbo.QORAccounts.RegistrationType, dbo.QORAccounts.Suspend,
> dbo.QORAccounts.DateAdded, dbo.Organizations.Registered,
> dbo.Organizations.DateRegistered FROM dbo.QORAccounts LEFT JOIN
> dbo.Organizations ON dbo.QORAccounts.ContactID =
> dbo.Organizations.ContactMemberID where
> dbo.QORAccounts.RegistrationType<>'OA' And

dbo.QORAccounts.ContentControlID

Quote:> = 209 Order By dbo.Organizations.DateRegistered asc;

> --

> Regards,
> Santosh