Any idea why this query always returns duplicate items?

Posted by Kardo on Stack Overflow See other posts from Stack Overflow or by Kardo
Published on 2014-08-20T09:39:49Z Indexed on 2014/08/20 10:20 UTC
Read the original article Hit count: 139

Filed under:
|
|
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about tsql