Problem with query

I have the following sql:
SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm, b.LastNm,
e.Processcatalog,
b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr, i.MaidenNm,
k.caseid, k.comments
FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid
INNER JOIN Processcatalog AS E ON A.process = E.processcatalogid
INNER JOIN Atts AS J ON A.id = J.caseid
INNER JOIN Users AS I ON J.userid = I.Userid
LEFT JOIN Users AS F ON A.empid = F.userid
LEFT JOIN CaseComments AS K ON a.id = k.Caseid
WHERE A.firmid = 2
ORDER BY i.userid
As soon as I added k.comments
I get this error:
Server: Msg 8163, Level 16, State 3, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.
Any ideas ?
AleksTry making the query without the Text field a subquery, aliased as, say Z,
and join it (Z) to the CaseComments table
Select Z.*, K.CaseID, K.comments
From (SELECT distinct a.id,a.caseid, b.userid AlienUserId,
b.FirstNm, b.LastNm, e.Processcatalog,
b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm AS EmployerName,
a.approvaldate, i.userid,i.MailStr, i.MaidenNm
From Cases A
Join Users B On A.alienid = B.userid
Join Processcatalog E On A.process = E.processcatalogid
Join Atts J On A.id = J.caseid
Join Users I On J.userid = I.Userid
Left Join Users F On A.empid = F.userid) As Z
Join CaseComments K On k.Caseid = Z.id
"Aleks" wrote:

> I have the following sql:
> SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm, b.LastN
m,
> e.Processcatalog,
> b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
> f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr, i.MaidenNm
,
> k.caseid, k.comments
> FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid
> INNER JOIN Processcatalog AS E ON A.process = E.processcatalogid
> INNER JOIN Atts AS J ON A.id = J.caseid
> INNER JOIN Users AS I ON J.userid = I.Userid
> LEFT JOIN Users AS F ON A.empid = F.userid
> LEFT JOIN CaseComments AS K ON a.id = k.Caseid
> WHERE A.firmid = 2
> ORDER BY i.userid
> As soon as I added k.comments
> I get this error:
> Server: Msg 8163, Level 16, State 3, Line 1
> The text, ntext, or image data type cannot be selected as DISTINCT.
>
> Any ideas ?
> Aleks
>
>|||uh uh, forgot your Where Clause, and Order By...
Select Z.*, K.CaseID, K.comments
From (SELECT distinct a.id,a.caseid, b.userid AlienUserId,
b.FirstNm, b.LastNm, e.Processcatalog,
b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm AS EmployerName,
a.approvaldate, i.userid,i.MailStr, i.MaidenNm
From Cases A
Join Users B On A.alienid = B.userid
Join Processcatalog E On A.process = E.processcatalogid
Join Atts J On A.id = J.caseid
Join Users I On J.userid = I.Userid
Left Join Users F On A.empid = F.userid
Where A.firmid = 2) As Z
Join CaseComments K On K.Caseid = Z.id
Order By Z.ID
"Aleks" wrote:

> I have the following sql:
> SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm, b.LastN
m,
> e.Processcatalog,
> b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
> f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr, i.MaidenNm
,
> k.caseid, k.comments
> FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid
> INNER JOIN Processcatalog AS E ON A.process = E.processcatalogid
> INNER JOIN Atts AS J ON A.id = J.caseid
> INNER JOIN Users AS I ON J.userid = I.Userid
> LEFT JOIN Users AS F ON A.empid = F.userid
> LEFT JOIN CaseComments AS K ON a.id = k.Caseid
> WHERE A.firmid = 2
> ORDER BY i.userid
> As soon as I added k.comments
> I get this error:
> Server: Msg 8163, Level 16, State 3, Line 1
> The text, ntext, or image data type cannot be selected as DISTINCT.
>
> Any ideas ?
> Aleks
>
>