Group Dataset and Find Desired Data

Okay, the scenario is, I have a Bubble table in database as follows:

[IMAGE_ID] [int] NOT NULL,
[X] [开发者_运维百科nchar](4) NOT NULL,
[Y] [nchar](4) NOT NULL,
[WIDTH] [nchar](4) NOT NULL,
[HEIGHT] [nchar](4) NOT NULL,
[TEXT] [nvarchar](max) NULL,
[USER_ID] [int] NOT NULL,
[DATE] [datetime] NOT NULL

A user can insert n bubble for an image. I select all bubbles for an image on image details since I'll be listing them as entries. Now I need to group these results and find a way to put 1 image for each user but still insert all user bubbles on the image. I couldn't think of a good way of doing this.

Do I have to do 2 selects : (1 distinct imageId + userId for image counts) and (1 usual select for bubbles)?

Thank you

---

This is possible with a recursive Common Table Expression. The first part of the CTE will pick up the record based on the image_id. This information you can then use to filter out the data which matches the USER_ID but not the IMAGE_ID (since that record is already returned by the first part of the query.)

;WITH bub (IMAGE_ID, X, Y, WIDTH, HEIGHT, TEXT, USER_ID, DATE)
AS ( SELECT IMAGE_ID, X, Y, WIDTH, HEIGHT, TEXT, USER_ID, DATE
     FROM Bubble
     WHERE IMAGE_ID = @image_id
     UNION 
     SELECT b.IMAGE_ID, b.X, b.Y, b.WIDTH, b.HEIGHT, b.TEXT, b.USER_ID, b.DATE
     FROM Bubble b
     JOIN bub bb
       ON b.USER_ID = bb.USER_ID
      AND b.IMAGE_ID <> bb.IMAGE_ID)
SELECT * FROM bub

Note: The ; in front of the WITH is required. It is required to terminate the previous statement with ; before your Common Table Expression. Putting the ; in front of the WITH is less error prone.