3

I have 3 tables. 1 Table (Table A) with patients of a certain disease (heart disease for example), and another table with patients (Table B) with all kinds of diseases. The third table is the results table.

What I want to do is compare heart disease patients from table A (table A is only heart disease patients) to kidney disease Patients in table B. IF the there is a hit (returns results) in table B, update the results table with an 'x' under column kidney disease for that ID.

The results table is currently in this format:

ID|Kidney|CHD|Diabetes ... all the way for another 10 types of diseases

To summarise: We check table A against table B with the specified disease name and if there is a return of result for that patientid, the results table updates with an 'x' under that disease name for that specific patientID. I will then rerun the query changing the disease names (from Kidney to cancer for example) to find out if the heart disease patient has cancer?

Sample Data

Table A

PatientID
1
2
3
4
5
.... all the way to 812 (812 patients of heart disease)

Table B

PatientID|Age|Disease1|Disease2|Disease3...all the way to Disease 10
1         50  kidney   lung
2         35  kidney   heart     diabetes
3         94  cancer   pneumonia CHD

What is meant to happen is, for every PatientID in Table A, we compare to table B for that specific disease (example kidney) from disease 1-10 in table B, if there is a result, we then update the results table with an 'x' under that specific disease column in the results table. So in our example, for patient 1 and 2 should have an 'x' under kidney.

With our sample data, the result table should look like this when we run for kidney:

ID|Kidney|CHD|Diabetes
 1     x
 2     x

Can anyone point me in the direction to writing the query for this? The query checks all patientID (812 of them) from table A for a specific disease in table B and if it returns results it updates for that disease and patient with an 'x' in the results table.

If there is a better way to do this, please advise me on how to go about it!

6

I propose implementing a different and more versatile database structure, because the addition of a (base) table that retains persons or patients that are affected by only one particular disease introduces update anomalies and is unnecessary.

Business rules

I consider that the following business rules formulations are of prime importance:

  • A Person is affected by zero-one-or-many Diseases
  • A Disease affects one-to-many Persons

Therefore, you want to implement a common many-to-many (M:N) relationship.

Illustrative logical model

Then, from such formulations, I have derived an IDEF1X1 logical model that is shown in Figure 1:

Figure 1 - People and Diseases Logical Model

As demonstrated, it is very important to define the entity types that are involved in the business domain under consideration, their attributes and how they are interrelated (including the cardinality of the relevant relationships).

In this case Person and Disease are independent entity types, each with their own attributes, and they are interrelated by way of an associative entity type, that I called PersonDisease, which holds the attributes that pertain to the relationship that takes place between the two mentioned entity types.

Each PersonDisease occurrence (or row once it is stored in a SQL database) is uniquely identified by PersonId along with DiseaseNumber, so I fixed this combination of attributes as the PRIMARY KEY of this associative entity type.

Derivable data

This model indicates that the Table B included in your answer is derivable data (obtained via DML operations that combine or compute columns from multiple tables), and not an entity type, neither independent nor associative; therefore you should not set it up as a base table.

Expository implementation

Consequently, I created the following DDL structure in terms of the logical model discussed above:

-- You should determine which are the most fitting 
-- data types and sizes for all your table columns 
-- depending on your business context characteristics.

-- Also, you should carry out some testing sessions to define 
-- the most convenient INDEXing strategies.

-- As one would expect, you are free to make use of 
-- your preferred (or required) naming conventions.

CREATE TABLE Person
(
    PersonId        INT       NOT NULL,
    FirstName       CHAR(30)  NOT NULL,
    LastName        CHAR(30)  NOT NULL,
    GenderCode      CHAR(3)   NOT NULL,
    BirthDate       DATE      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    CONSTRAINT PK_Person PRIMARY KEY (PersonId),
    CONSTRAINT AK_Person_FirstName_LastName_Gender_and_BirthDate UNIQUE -- Composite ALTERNATE KEY.
    (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    )
);

CREATE TABLE Disease
(
    DiseaseNumber    INT       NOT NULL,
    Name             CHAR(30)  NOT NULL,
    ParticularColumn CHAR(30)  NOT NULL,
    SpecificColumn   CHAR(30)  NOT NULL,
    Etcetera         CHAR(30)  NOT NULL,
    CreatedDateTime  TIMESTAMP NOT NULL,
    CONSTRAINT PK_Disease      PRIMARY KEY (DiseaseNumber),
    CONSTRAINT AK_Disease_Name UNIQUE      (Name)-- ALTERNATE KEY.
);

CREATE TABLE PersonDisease -- Associative table.
(
    PersonId        INT       NOT NULL,
    DiseaseNumber   INT       NOT NULL,
    DiagnosedDate   DATE      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    CONSTRAINT PK_PersonDisease                 PRIMARY KEY (PersonId, DiseaseNumber), -- Composite PRIMARY KEY.
    CONSTRAINT FK_from_PersonDisease_to_Person  FOREIGN KEY (PersonId)
        REFERENCES Person  (PersonId),
    CONSTRAINT FK_from_PersonDisease_to_Disease FOREIGN KEY (DiseaseNumber)
        REFERENCES Disease (DiseaseNumber)
);

This structure permits

  • storing n Disease instances (via simple INSERT operations) and
  • storing the relationship between n Person instances with n Disease instances.
  • managing Disease data independently of that which pertains to Person.

And avoids

  • employing ad hoc procedures that have to be carried out due to the update anomalies introduced by an additional disease-specific table.

Sample data

Let us suposse that the relevant database holds the data that follows:

-- 'Person' sample data.   
INSERT INTO 
    person (PersonId, FirstName, LastName, GenderCode, BirthDate, CreatedDateTime)
VALUES 
    (1, 'David', 'Smith', 'M', CURRENT_DATE, '20161031 13:14:02');

INSERT INTO 
    Person (PersonId, FirstName, LastName, GenderCode, BirthDate, CreatedDateTime)
VALUES 
    (2, 'Nicole', 'Johnson', 'F', CURRENT_DATE, '20161031 13:14:02');


-- 'Disease' sample data.
INSERT INTO 
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES 
   (1, 'Disease A', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO 
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES 
    (2, 'Disease B', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO 
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES 
    (3, 'Disease C', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO 
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES 
    (4, 'Disease D', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

-- 'PersonDisease' sample data.

INSERT INTO 
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES 
   (1, 1, '20161031', '20161031 13:14:02');

INSERT INTO 
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES 
    (1, 2, '20161031', '20161031 13:14:02');

INSERT INTO 
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES 
    (2, 1, '20161031', '20161031 13:14:02');

INSERT INTO 
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES 
   (2, 2, '20161031', '20161031 13:14:02');

INSERT INTO 
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES 
    (2, 3, '20161031', '20161031 13:14:02');

INSERT INTO 
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES 
(2, 4, '20161031', '20161031 13:14:02');

So you might like to retrieve all the diseases by which all the persons are affected via, e.g., the statement displayed bellow:

SELECT Person.FirstName            AS "Patient first name",
       Person.LastName             AS "Patient last name",
       Disease.Name                AS "Disease name",
       PersonDisease.DiagnosedDate AS "Diagnosed date"
  FROM PersonDisease
  JOIN Disease
    ON Disease.DiseaseNumber = PersonDisease.DiseaseNumber
  JOIN Person
    ON Person.PersonId       = PersonDisease.PersonId; 

As is also exemplified in this SQL Fiddle.

Inspecting all the other diseases suffered by persons that have heart disease

Let us assume that heart disease is primarily identified by disease number 3, so you can construct a SELECT statement like, e.g., the following one to see what other diseases are suffered by people with heart disease:

SELECT PersonDisease.PersonId,
       Person.FirstName,
       Person.LastName,
       PersonDisease.DiseaseNumber,
       Disease.Name
  FROM PersonDisease
  JOIN Person
    ON Person.PersonId              = PersonDisease.PersonId
  JOIN Disease
    ON Disease.DiseaseNumber        = PersonDisease.DiseaseNumber
 WHERE Person.PersonId              IN (SELECT PersonId
                                          FROM PersonDisease
                                         WHERE DiseaseNumber = 3)
   AND PersonDisease.DiseaseNumber <> 3;

You can review a comparable method in action in this new SQL Fiddle.

Of course, this and the query discussed in the preceding section can be defined as VIEWs so that you can SELECT data directly FROM them, if necessary.

There might well be other approaches to solve this requirement like, e.g., comparing the data stored in PersonDisease with a VIEW that includes only the persons who have heart disease, or a CTE as suggested by @Joishi Bodio in their answer. Some of them could be served by faster execution plans than the ones that correspond to the other approaches, so you should naturally make some tests and implement a reliable INDEXing strategy.

Using the CROSSTAB() Function

It appears that you want to construct some kind of PIVOT query, so you may find of help this Stack Overflow answer in which @Erwin Brandsteter details several usages of the CROSSTAB() function that, according to the PostgreSQL documentation, is included in the tablefunc module.

Answer focus

The objective should be, as discussed above, to avoid appending the superfluous disease-specific base table.


Endnote

1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical work authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.

0

A better way would be a normalized structure..

  1. Patient table - info with regard to patient
  2. Disease table - into with regard to disease
  3. Patient Disease table (join between the above two) - probably only two cols, patient id and disease id

Your query to figure out all diseases patients with heart disease have is simple at this point.

Let's assume heart disease is disease_id = 1

WITH heart_disease_patients AS (
  SELECT DISTINCT patient_id FROM patient_disease WHERE disease_id = 1
) SELECT
  patient_id, disease_name
FROM PATIENT_DISEASE pd
JOIN heart_disease_patients hdp USING (patient_id)
JOIN DISEASE d USING (disease_id)
  • the original structure is similar to what you have posted. But I later queried my "persondisease" table and stored only the IDs of patients with heart disease into another table. Now that I have ONLY heart disease patients, I want to see what other diseases these heart patients have. Does that make sense? Appreciate your help – timz Nov 1 '16 at 21:22
  • 2
    My answer is irrelevant compared to what @MDCCL proposes (which is the same thing, but with much more explanation) My reply to this is the same as his, though - "Only heart disease" .. you should have all, including heart disease, in the patient_disease table.. (read my SQL query again more closely..) – Joishi Bodio Nov 1 '16 at 21:56
  • everything including heart disease is in the patient_Disease table. I queried the patient_disease table to extract ONLY heart disease patients so I can use those IDs to further query the patient_disease table and see what other diseases these heart disease patients have. Does that make sense? Appreciate the help! – timz Nov 1 '16 at 22:15
  • @timz The query I provided does pretty much exactly what you are saying.. – Joishi Bodio Nov 1 '16 at 22:45
  • Ok thanks What about if i want to see if heart disease patients have kidney disease, do I just add that in the where clause? Also is there a way to create a final table, like the one in my opening post, which can then show what other diseases heart disease patients can have, and this is marked with 'x'? – timz Nov 1 '16 at 23:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.