SELECT DISTINCT 1

[Luzian]

Error:

Msg 421, Level 16, State 1, Procedure sp_GetMessages, Line 9
The text data type cannot be selected as DISTINCT because it is not comparable.

Simple:
With a table called "[tt]Messages[/tt]" I've a column called "[tt]id[/tt]" of type [tt]int[/tt], a column called "[tt]owner[/tt]" of type [tt]int[/tt], and a column called "[tt]content[/tt]" of type [tt]text[/tt]

Consider the query:
(raises the error)

SELECT * FROM [Messages]
UNION
SELECT * FROM [Messages]

and
(doesn't raise error, but returns duplicate records, in which is an undesirable result)

SELECT * FROM [Messages]
UNION [b]ALL[/b]
SELECT * FROM [Messages]

Although this query is not practical as it is redundant, it is used to raise the error. For whatever reason, pretend that this is what I wanted to do, why can't I use [tt]UNION[/tt] to eliminate duplicate rows? I assume it's because of the column "content" that is of text type. Well I don't give a crap about comparing text types. Why can't it just compare on the "id" column to check for duplicates, or should I ask: why can't I explicitly tell the query I want it to only compare on the "id" column? Maybe I'm going about this the wrong way, maybe there's a more logical solution to my problem. If so, please let me know.


detailed:

Create schema:

CREATE TABLE [Users]
(
	[id] int IDENTITY NOT NULL PRIMARY KEY,
	[name] varchar(20) NOT NULL UNIQUE
)
GO

CREATE TABLE [Messages]
(
	[id] int IDENTITY NOT NULL PRIMARY KEY,
	[owner] int NOT NULL FOREIGN KEY REFERENCES [Users]([id]),
	[content] text
)
GO

CREATE TABLE [Users_Messages] -- mapping table
(
	[message_id] int NOT NULL FOREIGN KEY REFERENCES [Messages]([id]),
	[user_id] int NOT NULL FOREIGN KEY REFERENCES [Users]([id])
)
GO

Create two functions that returns results from the "[tt]Messages[/tt]" table.

CREATE FUNCTION dbo.fn_GetAccessableMessages
(
	@accessor int -- user id of the user who has access to these messages
)
RETURNS TABLE
AS
RETURN
(
	SELECT m.*
	FROM [Messages] m
	JOIN [Users_Messages] u_m ON m.[id] = u_m.[message_id]
	WHERE u_m.[user_id] = @accessor
)
GO

CREATE FUNCTION dbo.fn_GetOwnedMessages
(
	@owner int -- user id of the owner of these messages
)
RETURNS TABLE
AS
RETURN
(
	SELECT *
	FROM [Messages]
	WHERE [owner] = @owner
)
GO

Create a stored procedure that unifies the two functions via the [tt]UNION ALL[/tt] keyword, which I don't want to use. I'd rather use only the [tt]UNION[/tt] keyword, but I can't due to the error.

CREATE PROCEDURE dbo.sp_GetMessages
(
	@accessor int
)
AS
BEGIN
	SELECT * FROM dbo.fn_GetAccessableMessages(@accessor)
	UNION ALL
	SELECT * FROM dbo.fn_GetOwnedMessages(@accessor)
END
GO

Create test data.

DECLARE @user_id int
DECLARE @message_id int

-- create a user
INSERT INTO [Users] ([name])
VALUES ('bob')
SET @user_id = @@IDENTITY

-- create a message
INSERT INTO [Messages] ([owner], [content])
VALUES
(
	@user_id,
	'blah blah blah'
)
SET @message_id = @@IDENTITY

-- grant bob access to this message
INSERT INTO [Users_Messages] ([user_id], [message_id])
VALUES
(
	@user_id,
	@message_id
)
GO

Now executing the stored procedure:

EXEC	@return_value = [dbo].[sp_GetMessages]
		@accessor = 1

GO

will result in duplicate records:

[b]id[/b]      [b]owner[/b]   [b]content[/b]
1       1       blah blah blah
1       1       blah blah blah

 

[r937]

select * 
  from messages
 where id in 
       ( select id 
           from messages
          where owner = @accessor
         union 
         select message_id
           from user_messages
          where user_id = @accessor 
       ) as unique_ids

r937.com | rudy.ca

 

[Luzian]

Thanks, that worked