PatientName DateOftest Eye L...">
Clever Geek Handbook

Selecting Individual Members in a Restricted Classroom

I have a table called "Patients" that looks like this:

> PatientName      DateOftest    Eye     L1    L2    L3    L4     L5
>     Mike         17-02-2009     L      23    25    40    32     30
>     Mike         17-02-2009     R      25    30    34    35     24
>     Ryan         18-03-2012     R      12    18    27    39     40
>     Bill         08-03-2006     L      20    24    30    24     25
>     Bill         08-03-2006     R      18    25    27    30     24   
>     Chan         03-08-2009     L      18    21    28    35     12

No, I have to choose * in patients who only have one (either the left eye or the right eye control test). The result would be

Ryan         18-03-2012     R      12    18    27    39     40
Chan         03-08-2009     L      18    21    28    35     12
+3


source to share


4 answers


You can do it:

SELECT *
FROM Patients
WHERE PatientName IN(SELECT PatientName 
                     FROM Patients
                     GROUP BY PatientName 
                     HAVING COUNT(*) = 1);

Demo SQL Fiddle



This will give you:

| PATIENTNAME | DATEOFTEST | EYE | L1 | L2 | L3 | L4 | L5 |
-----------------------------------------------------------
|        Ryan | 18-03-2012 |   R | 12 | 18 | 27 | 39 | 40 |
|        Chan | 03-08-2009 |   L | 18 | 21 | 28 | 35 | 12 |
+3


source


Another option with EXISTS operator

SELECT *
FROM Patients p1
WHERE EXISTS (
              SELECT 1
              FROM Patients p2
              WHERE p1.PatientName = p2.PatientName
              HAVING COUNT(*) = 1
              )


Demo on SQLFiddle @Mahmoud Gamal thanks for the DDL schema;)

+1


source 


SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  PatientName
            FROM    tablename
            GROUP   BY PatientName
            HAVING  COUNT(Eye) = 1
        ) b ON a.PatientName = b.PatientName

add INDEX

in column for better performance PatientName

.

COUNT(Eye)

would be best for patients who have the same recorded eye

but different ones DateOftest

.

0


source


You can try this

SELECT PatientName, 
       DateOftest, 
       MIN(Eye) AS Eye, 
       MIN(L1) AS L1, MIN(L2) AS L2, MIN(L3) AS L3, MIN(L4) AS L4, MIN(L5) AS L5
FROM patients
GROUP BY PatientName, DateOftest
HAVING COUNT(Eye) = 1

Result

| PATIENTNAME | DATEOFTEST | EYE | L1 | L2 | L3 | L4 | L5 |
-----------------------------------------------------------
|        Chan | 03-08-2009 |   L | 18 | 21 | 28 | 35 | 12 |
|        Ryan | 18-03-2012 |   R | 12 | 18 | 27 | 39 | 40 |

sqlfiddle

0


source






More articles:


All Articles