2

I have three tables Patients, which contains the name of my patients, Controls which represents the UI controls which could be drawn for each illness , and ControlsValues table which contains the values of the controls submitted for each Patient

Lets have some data Patients Table

|ID  | Name |
|-----------|
| 1  | Ara  |
| 2  | Sada |

Controls table

|ID  | Text | Type     |
|-----------|----------|
| 1  | age  | textbox  |
| 2  |alergy| checkbox |

Then the controlsValues table which is where I want to query at

|ID  | contrlId | value    | patientId |
|---------------|----------|-----------|
| 1  | 1        | 23       | 1         |
| 2  | 2        | true     | 1         |
| 3  | 1        | 26       | 2         |
| 4  | 2        | false    | 2         |

here my problem occurs when I want to return that patient from ControlsValues table that has the (controlId=1 AND value=23) and (controlId=2 AND value=true) in this case the condition is on two rows not two columns which it is not possible , so I desided to change the rows into the columns depending on controlId but I dont know how and I have been searching for 2 days and seen a lot of samples but none of them helped me to solve my problem

3
  • What is the query you tried so far ?
    – pikai
    May 13 '15 at 12:42
  • @MySQLRockstar i have tried many none worked , do not know which one to put May 13 '15 at 13:00
  • Install this Stored Procedure and it will generate the SELECT you need.
    – Rick James
    Jun 7 '15 at 14:30
1

You can try this kind of "solution":

http://sqlfiddle.com/#!9/d33a95/18

It is not an "pivot-table" solution, but maybe it solves your problem - if I have understood it correctly.

Here are the tables / data used:

CREATE TABLE `patients` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
);
CREATE TABLE `controls` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Text` VARCHAR( 50 ) NOT NULL ,
`Type` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
); 
CREATE TABLE `controlvalues` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`ControlID` INT NOT NULL ,
`Value` VARCHAR( 50 ) NOT NULL ,
`PatientID` INT NOT NULL ,
PRIMARY KEY ( `ID` )
);
INSERT INTO `patients` (`Name`) VALUES ('Ara'), ('Sada'), ('Pada'), ('Lada');
INSERT INTO `controls` (`Text`, `Type`) VALUES ('age', 'textbox'), ('alergy', 'checkbox');
INSERT INTO `controlvalues` (`ControlID`, `Value`, `PatientID`) VALUES 
  (1, '23', 1), 
  (2, 'true', 1),
  (1, '25', 2),
  (2, 'false', 2),
  (1, '23', 3),
  (2, 'false', 3),
  (1, '23', 4), 
  (2, 'true', 4);

And the query:

SELECT PatientID, COUNT(PatientID) AS PatCount, p.Name
FROM controlvalues cv
LEFT JOIN patients p ON p.ID = cv.PatientID
WHERE ((ControlID = 1 AND Value = '23')
   OR (ControlID = 2 AND Value = 'true'))
GROUP BY PatientID
HAVING PatCount = 2
;
3
  • I have tried this query before but it returns every patient that their age is 23 or they have allergy but I need the patients that have both allergy and they are 23 years old May 13 '15 at 13:07
  • Sure? I have edited my code above (added two more patients) and it gives back only PatID 1 and 4, as it should. But you can not test it on SQL Fiddle ( sqlfiddle.com/#!9/318e2/18 ) because of an error there... Test it locally on your dev enviroment.
    – Billy G
    May 13 '15 at 13:23
  • @Billy - Not sure why join and group by is introduced. This should be fine I guess. Please correct me if I'm wrong. select * from controlvalues WHERE ((ControlID = 1 AND Value = '23') OR (ControlID = 2 AND Value = 'true'))
    – Mannoj
    Jun 27 '17 at 2:47
1

Exists

You can get your answer without needing to pivot your data. The most direct approach (from my perspective) is using EXISTS, which reflects very directly your statement:

SELECT
     p.ID, p.Name
FROM
     patients p
WHERE
    EXISTS 
    (
        SELECT 1
        FROM controlvalues cv1
        WHERE p.ID = cv1.PatientID
              AND cv1.controlId = 1
              AND cv1.value = '23'
    )
    AND
    EXISTS
    (
        SELECT 1
        FROM controlvalues cv2
        WHERE p.ID = cv2.PatientID
              AND cv2.controlId = 2
              AND cv2.value = 'true'
    ) ;

Which will just return:

ID | Name
-: | :---
 1 | Ara

Join

As an alternative, you can change the EXISTS to JOINs:

SELECT
     p.ID, p.Name
FROM
     patients p
     JOIN controlvalues cv1 
         ON cv1.PatientID = p.ID AND cv1.controlId = 1 AND cv1.value = '23'
     JOIN controlvalues cv2 
         ON cv2.PatientID = p.ID AND cv2.controlId = 2 AND cv2.value = 'true' ;

which will return the same results. The query is more concise, but, in my opinion a little bit more difficult to read.

The execution plans (as of MariaDB 10.2) are the same, provided the right indexes (or, in my case, the primary key) have been defined.


Pivot

If you really want to pivot your data, that's how you would do it:

SELECT
    p.ID, p.Name,
    max(case when controlId = 1 then value end) AS controlId1 /* age */,
    max(case when controlId = 2 then value end) AS controlId2 /* allergy */
FROM
    patients p
    JOIN controlvalues cv ON cv.PatientId = p.ID 
GROUP BY
    p.ID ;

This is the pivoted table:

ID | Name | controlId1 | controlId2
-: | :--- | :--------- | :---------
 1 | Ara  | 23         | true      
 2 | Sada | 25         | false

... and that's how you would use it:

SELECT
    pp.ID, pp.Name
FROM
    (
    SELECT
        p.ID, p.Name,
        max(case when controlId = 1 then value end) AS controlId1,
        max(case when controlId = 2 then value end) AS controlId2
    FROM
        patients p
        JOIN controlvalues cv ON cv.PatientId = p.ID 
    GROUP BY
        p.ID 
    ) AS pp
WHERE
    pp.controlId1 = '23' AND pp.controlId2 = 'true';
ID | Name
-: | :---
 1 | Ara 

The execution plan for the pivoted table, however, might probably be worse (specially in a big table).

You can check everything in dbfiddle here

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.