Any idea why this query always returns duplicate items?
- by Kardo
I want to get all Images not used by current ItemID. The this subquery but it also always returns duplicate Images:
EDITED
select Images.ImageID, Images.ItemStatus, Images.UserName, Images.Url,
Image_Item.ItemID, Image_Item.ItemID
from Images
left join (select ImageID, ItemID, MAX(DateCreated) x
from Image_Item
where ItemID != '5a0077fe-cf86-434d-9f3b-7ff3030a1b6e'
group by ImageID, ItemID
having count(*) = 1)
image_item on Images.imageid = image_item.imageid
where ItemID is not null
I guess the problem is with the subquery which I can't avoid duplicate rows:
select ImageID, ItemID, MAX(DateCreated) x
from Image_Item
where ItemID != '5a0077fe-cf86-434d-9f3b-7ff3030a1b6e'
group by ImageID, ItemID
having count(*) = 1
Result:
F2EECBDC-963D-42A7-90B1-4F82F89A64C7 0578AC61-3C32-4A1D-812C-60A09A661E71
F2EECBDC-963D-42A7-90B1-4F82F89A64C7 9A4EC913-5AD6-4F9E-AF6D-CF4455D81C10
42BC8B1A-7430-4915-9CDA-C907CBC76D6A CB298EB9-A105-4797-985E-A370013B684F
16371C34-B861-477C-9A7C-DEB27C8F333D 44E6349B-7EBF-4C7E-B3B0-1C6E2F19992C
Table: Images
ImageID uniqueidentifier
UserName nvarchar(100)
DateCreated smalldatetime
Url nvarchar(250)
ItemStatus char(1)
Table: Image_Item
ImageID uniqueidentifier
ItemID uniqueidentifier
UserName nvarchar(100)
ItemStatus char(1)
DateCreated smalldatetime
Any kind help is highly appreciated.
