DISTINCT with text datatype columns

[Rickspx]

Hi,

Is there any method to achieve DISTINCT with text datatype columns.
Sql doesnt allow DISTINCT with text columns.Even though I dont
need to use distinct directly to a TEXT column, text columns which is
part of a join is creating problems

Thanks
Rick

 

[swampBoogie]

Need a little rundown on tables involved and the query you are attempting.

What are you trying to accomplish?

 

[SQLSister]

I'm curious too because as a rule you wouldn't want to join on text fields.

 

[Rickspx]

Here it is

SELECT distinct recep.id AS rID,

recep.NUM, recep.remark,
recep.name, recep.year

FROM recep
INNER JOIN recepType ON recep.TypeID = receptype.id
LEFT OUTER JOIN Calrecep ON recep.year = Calrecep.year
AND recep.NUM = Calrecep.NUM

Recep.Remark is the text field


Rick

 

[swampBoogie]

Maybe something like

SELECT recep.id AS rID,
recep.NUM, recep.remark,
recep.name, recep.year
from recep
where typeid in (
select id from receptype)
and exists( select * from Calrecep
where recep.year = Calrecep.year
AND recep.NUM = Calrecep.NUM )
union all
SELECT recep.id AS rID,
recep.NUM, recep.remark,
recep.name, recep.year
from recep
where typeid in (
select id from receptype)
and not exists( select * from Calrecep
where recep.year = Calrecep.year
AND recep.NUM = Calrecep.NUM )

The outer join makes it more complex.

 

[tlbroadbent]

No columns are selected from Calrecep and the JOIN is a LEFT JOIN so a matching row in the table is not needed. Drop the LEFT JOIN and the Calrecep table from the query.

No columns are selected from recepType so the INNER JOIN is unneeded. You can use WHERE EXISTS instead. Modify the query as follows.

SELECT
recep.id AS rID,
recep.NUM,
recep.remark,
recep.name,
recep.year

FROM recep
WHERE Exists
(Select * From recepType
Where recep.TypeID = receptype.id)

I suspect you may tell us that the query was simplified for posting here and you actually select columns from the two additional tables. In that case, I recommend posting a complete query rather than a stripped down model that is incomplete.

If the query is complete then my revision should work unless multiple rows exist in the Recep table for the selected columns and you want distinct rows. If you don't need to return more than 8000 characters in the remark column, you can try the following.

SELECT
recep.id AS rID,
recep.NUM,
Convert(varchar(8000), recep.remark) As remark,
recep.name,
recep.year

FROM recep
WHERE Exists
(Select * From recepType
Where recep.TypeID = receptype.id) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.