I have a table
which is joined to another table
The Owner can be a Participant, and if it is, the same reference (into user table) is in Owner and Participant. So I did:
SELECT TableA.Id,TableA.Owner,TableA.Text
FROM TableA
WHERE TableA.Owner=@User
UNION
SELECT TableA.Id,TableA.Owner.TableA.Text
FROM TableA LEFT JOIN TableB ON (TableA.Id=TableB.Id)
WHERE TableB.Participant = @User
This query should return all distinct data sets where a certain @User is either Owner or Participant or both.
And it would, if SQL Server wouldn't throw
The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
Since Id is a PK, and Text is from the same table, why would SQL Server want to compare Text at all?
I can use UNION ALL
to stop duplicate detection, but can I circumvent this without losing the distinctness of the results?
Correct way
Stop using TEXT
it is obsolete. Alter table schema.
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
Workaround
Cast to NVARCHAR(MAX)
:
SELECT TableA.Id,TableA.Owner, CAST(TableA.DescriptionText AS NVARCHAR(MAX))
FROM TableA
WHERE TableA.Owner=@User
UNION
SELECT TableA.Id,TableA.Owner, CAST(TableA.DescriptionText AS NVARCHAR(MAX))
FROM TableA LEFT JOIN TableB ON (TableA.Id=TableB.Id)
WHERE TableB.Participant = @User