I cannot select distinct when I inner join to other tables.

Question

Post reply

I cannot select distinct when I inner join to other tables.

BKW

Ten Centuries

Points: 1350
More actions
July 14, 2008 at 11:00 am

#381659

Need help asap? Need to user this for SSRS :unsure:
Table_01
Log# TextID Name TextType
12 10 Joe 7
12 11 Joe 7
12 12 Joe 7
12 13 Joe 7
12 14 Joe 7
13 17 Don 4
14 21 Sue 7
14 22 Sue 7
14 23 Sue 7
15 44 Ann 8
Table_02
TypeID Desc
10 My'Text
11 My'Text
12 My'Text
13 My'Text
14 My'Text
17 Don'Text
21 Sue'Text
22 SueText
23 AnnText
Table_03
TextType Category
4 DDL
8 XML
7 DML
Here is query:
select
Log#,
TextID,
Name,
Desc,
T.TextType,
S.Category
From Table_01 E
inner join Table02 T on T.TypeID = E.TypeID
inner join Table_03 S on S.TextType = E.TextType
where T.category = 'DML' and E.Name In ('Joe'. 'Sue')
order by Log#
This what I get
Log TextID Name Desc TextType Category
12 10 Joe My Text 7 DML
12 11 Joe My Text 7 DML
12 12 Joe My Text 7 DML
12 13 Joe My Text 7 DML
12 14 Joe My Text 7 DML
14 21 Sue Sue Text 7 DML
14 22 Sue Sue Text 7 DML
14 23 Sue Sue Text 7 DML
This is what I need:
Log Name Desc TextType Category
12 Joe My Text 7 DML
14 Sue Sue Text 7 DML
Changinagain

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

Try replacing

select

Log#,

TextID,with...

select distinct Log#,

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

BKW Ten Centuries Points: 1350 More actions · Answer 2

Did that, that is how I got the error:

The ntext data type cannot be selected as DISTINCT because it is not comparable.

Changinagain

BKW Ten Centuries Points: 1350 More actions · Answer 3

I figured it out. ntext does not work well in SQL2005. I had to convert to nvarchar. Works fine now.

Changinagain

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

NText works just fine in SQL 2005 (although it is deprecated).

The blob datatypes (text, ntext, image) cannot be directly compared (including by a distinct) in any version of SQL that they appear. It's a flaw of the data types

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

J-440512 SSCertifiable Points: 6285 More actions · Answer 5

I am not certain this can really be called a flaw.

SELECT DISTINCT ntext

would mean for each record in the query, go get the text stored at pointer of ntext and compare them all ...

This would most likely be a serious performance issue.

I have the seen ntext and blob types abused to store xml or other kind of composite data that has to be parsed to separate its various components.

What did you store in ntext ? do you really have to compare it too ?

Regards

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

J (7/16/2008)
I am not certain this can really be called a flaw.
SELECT DISTINCT ntext
would mean for each record in the query, go get the text stored at pointer of ntext and compare them all ...

Yup. No different to SELECT DISTINCT nvarchar(max)... which is permitted in SQL 2005.

Never said it was a good idea. 😉

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

J-440512 SSCertifiable Points: 6285 More actions · Answer 7

I hope I did not leave the impression that you saw it as a good idea. I just pointed out it is not a flaw that it does not work, rather it looks to me like NOT allowing this was a good idea.

Regards