CODEWITHSUNDEEP
c#sqlsql-server
Max Kirsch
Max Kirsch

Reputation: 461

ASP.NET SQL elimininate duplicate Ids

I have two database tables for documenting a wound healing progression. Those are joined over the wound_id-Column like this:

So for one wound, I can create many progresses to show the healing of it. This is working fine.

Here is the code for the tables:

Table wound_details:

CREATE TABLE [dbo].[epadoc_mod_wound_details] (
    [wound_id]          INT           IDENTITY (1, 1) NOT NULL,
    [wound_type]        VARCHAR (500) NULL,
    [wound_description] VARCHAR (500) NULL,
    [decuGrade]         INT           NULL,
    [wound_comments]    VARCHAR (500) NULL,
    [wound_timeReal]    DATETIME      NULL,
    [wound_timeGiven]   DATETIME      NULL,
    [casenumber]        INT           NULL,
    [username]          VARCHAR (50)  NULL,
    [infectionstate]    VARCHAR (50)  NULL,
    PRIMARY KEY CLUSTERED ([wound_id] ASC)
);

Table wound_progress:

CREATE TABLE [dbo].[epadoc_mod_wound_progress] (
    [progress_id]       INT           IDENTITY (1, 1) NOT NULL,
    [wound_length]      INT           NULL,
    [wound_width]       INT           NULL,
    [wound_depth]       INT           NULL,
    [wound_surrounding] VARCHAR (500) NULL,
    [wound_consistence] VARCHAR (500) NULL,
    [wound_state]       VARCHAR (200) NULL,
    [wound_painscale]   INT           NULL,
    [wound_itch]        INT           NULL,
    [wound_id]          INT           NULL,
    PRIMARY KEY CLUSTERED ([progress_id] ASC),
    CONSTRAINT [FK_epadoc_mod_wound_progress_fk] FOREIGN KEY ([wound_id]) REFERENCES [dbo].[epadoc_mod_wound_details] ([wound_id]) ON DELETE CASCADE
);

Then I wrote a SELECT-Query to show all wounds for specific case number which are documented for the patient:

SELECT DISTINCT
    dbo.epadoc_mod_wound_details.wound_id, dbo.epadoc_mod_wound_details.casenumber, dbo.epadoc_mod_wound_details.wound_type, dbo.epadoc_mod_wound_progress.progress_id, dbo.epadoc_mod_wound_details.wound_comments, dbo.epadoc_mod_wound_details.wound_timeReal, dbo.epadoc_mod_wound_details.username    
FROM dbo.epadoc_mod_wound_details LEFT JOIN 
     dbo.epadoc_mod_wound_progress
     ON dbo.epadoc_mod_wound_details.wound_id = dbo.epadoc_mod_wound_progress.wound_id
WHERE dbo.epadoc_mod_wound_details.casenumber = @casenr;

This is working fine though, but the problem is that ALL wound progresses are shown in the GridView, here is an example so you can see what I mean:

GridView Screenshot

What I want to do is just show the latest progress of one wound, so for the above example just show the last entry with progressID 65:

33     65          1111111  Dekubitus   
34         ..          .......  .........

The SELECT DISTINCT approach didn't work and I also tried with MAX(progressID) but I always seem to get errors. I think I have to do something with ORDER BY or a second SELECT-Query before the JOIN.

Thanks for any advice!

Upvotes: 0

Views: 72

Answers (3)

Saeid Amini
Saeid Amini

Reputation: 1307

I understand you need each record of "epadoc_mod_wound_details" with the latest record of "epadoc_mod_wound_progress".

You can try this:

    select wound.wound_id, wound.casenumber, wound.wound_type, 
    wound.wound_comments, wound.wound_timeReal, wound.username, MAX(progress_id) 
    from epadoc_mod_wound_details wound
    left join epadoc_mod_wound_progress progress on wound.wound_id = progress.wound_id
    where wound.casenumber = ''
    group by wound.wound_id, wound.casenumber, wound.wound_type,
    wound.wound_comments, wound.wound_timeReal, wound.username

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82514

Since you only want the progress_id, The easies way to do it is using a correlated subquery:

SELECT  wound_id, 
        casenumber, 
        wound_type, 
        (
            SELECT TOP 1 progress_id
             FROM dbo.epadoc_mod_wound_progress AS WP
             WHERE WP.wound_id = WD.wound_id
             ORDER BY progress_id
        ) As progress_id, 
        wound_comments, 
        wound_timeReal, 
        username    
FROM dbo.epadoc_mod_wound_details As WD
WHERE casenumber = @casenr;

Upvotes: 1

Flavio Francisco
Flavio Francisco

Reputation: 775

You should use GROUP BY combined with MAX in your query.

SELECT  
   dbo.epadoc_mod_wound_details.wound_id, 
   dbo.epadoc_mod_wound_details.casenumber, 
   dbo.epadoc_mod_wound_details.wound_type, 
   MAX(dbo.epadoc_mod_wound_progress.progress_id) AS progress_id, 
   dbo.epadoc_mod_wound_details.wound_comments, 
   dbo.epadoc_mod_wound_details.wound_timeReal, 
   dbo.epadoc_mod_wound_details.username    
FROM 
  dbo.epadoc_mod_wound_details 
LEFT JOIN 
  dbo.epadoc_mod_wound_progress ON 
  dbo.epadoc_mod_wound_details.wound_id = dbo.epadoc_mod_wound_progress.wound_id 
GROUP BY
   dbo.epadoc_mod_wound_details.wound_id, 
   dbo.epadoc_mod_wound_details.casenumber, 
   dbo.epadoc_mod_wound_details.wound_type, 
   dbo.epadoc_mod_wound_details.wound_comments, 
   dbo.epadoc_mod_wound_details.wound_timeReal, 
   dbo.epadoc_mod_wound_details.username;

Upvotes: 1

Related Questions