The data type image cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable

Question

SELECT cars.carid,cars.variant,  cars.make, cars.model, cars.condition, cars.amount,
       cars.statuss, img.img, seller.fname, seller.lname
FROM img
  INNER JOIN cars
  ON img.carid = cars.carid
  inner join seller
  on seller.sid= cars.sid
where cars.status =1
union
SELECT cars.carid,cars.variant,  cars.make, cars.model, cars.condition, cars.amount,
       cars.statuss, img.img, dealer.fname, dealer.lname
FROM img
  INNER JOIN cars
  ON img.carid = cars.carid
  inner join  dealer
  on dealer.did=cars.did
where cars.status =1

Query not working. I want to make both the query work at the same time.

Possible duplicate of [The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable](http://stackoverflow.com/questions/34897677/the-data-type-text-cannot-be-used-as-an-operand-to-the-union-intersect-or-excep) — TheGameiswar, Dec 09 '16 at 08:43
@ajeh Yah, as there's no comparison involved that way... good point. — TT., Dec 09 '16 at 18:36

TT. · Answer 1 · 2016-12-09T17:36:18.013

You should be abandoning the IMAGE datatype as it is deprecated at this point.

IMPORTANT! ntext, text, and image data types will be removed in a future version of 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.

Change the datatype of the image columns in the tables to VARBINARY(MAX), or cast the images in your select query to VARBINARY(MAX) to solve your problem.

score 0 · Answer 2 · answered Dec 09 '16 at 15:56

The error message is telling you what the problem is. If we employ some lateral thinking, we can get around it though. Looking at your query, you're trying to find car details that match either your sellers or dealers. Right now, your query says "get me all of the details for cars that match sellers and all of the details that match dealers". Instead, we can write it as "find me a list of car IDs that match either dealers or sellers and then find the car details for those". Here's how I'd write it.

select cars.carid,cars.variant, cars.make, cars.model, cars.condition, cars.amount,
       cars.statuss, img.img, c.lname, c.fname
from img
join (
    select cars.carid, dealer.fname, dealer.lname
    FROM cars
    inner join  dealer
        on dealer.did=cars.did
    where status = 1

    union

    select cars.carid, seller.fname, seller.lname
    FROM cars
    inner join  seller
        on seller.sid=cars.sid
    where status = 1
) as c
    on img.carid = c.carid
join cars
    on c.carid = cars.carid

The data type image cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable

2 Answers2