Solved

Query syntax

Posted on 2014-10-16
13
166 Views
Last Modified: 2014-10-17
I have a table (EyeData) that contains the columns patientid, Eye (left or right), enucleation

There can be multiple rows for a patient.  There can also be records that have Eye=right and Eye=Left for the same patient.

How would i select the rows where the patient only has rows for left or rows for right?  I.e. to put it simply where the patient has only data for one eye.
0
Comment
Question by:soozh
  • 3
  • 3
  • 3
  • +3
13 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40384590
Copy-paste the below T-SQL into your SSMS, change the column names to fit your table schema, and let 'er rip...
SELECT l.patiendid, l.eye,
FROM EyeData l
   LEFT JOIN EyeData r ON l.patientID = r.patient_id
WHERE l.eye='Left' and r.eye IS NULL
UNION ALL
SELECT l.patiendid, l.eye,
FROM EyeData r
   LEFT JOIN EyeData l ON l.patientID = r.patient_id
WHERE r.eye='Right' and l.eye IS NULL

Open in new window

0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40384595
If I understood your question a simple query like this one could be the solution:
SELECT *
FROM Patient
WHERE Eye IS NOT NULL

Open in new window

0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
ID: 40384625
Jim, line 6 refers to the wrong tables (and "patiendid" is mispelt in lines 1 and 6)- it should be:

SELECT l.patientid, l.eye,
FROM EyeData l
   LEFT JOIN EyeData r ON l.patientID = r.patient_id
WHERE l.eye='Left' and r.eye IS NULL
UNION ALL
SELECT r.patientid, r.eye,
FROM EyeData r
   LEFT JOIN EyeData l ON l.patientID = r.patient_id
WHERE r.eye='Right' and l.eye IS NULL

Open in new window

0
 

Author Comment

by:soozh
ID: 40384633
thanks.

Jim i could see that your solution would work (apart from the typos) but i was originally thinking along the lines of Group By and Having.

I started with:
SELECT 
      [PersonId],
      max(Enukleation) as Enucleation
 FROM EyeData
GROUP BY PersonId
HAVING ( COUNT(PersonId) = 1 )

Open in new window


By this excluded the patients that had more than eye registered.

Is there some way to do it using group by/having?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40384640
Also, there is patientID and patient_ID, which should be the same.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40384654
That seems to work, assuming Enucleation is an integer which represents the left-eye or right-eye.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 40384670
thanks for the typo corrections.

>but i was originally thinking along the lines of Group By and Having.
Nope, as 'left not right' and 'right not left' can only be done via a LEFT JOIN and WHERE right table IS NULL'.

The HAVING COUNT(..) = 1 is sound logic, until you come across the guy with two left eyes.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40385106
SELECT e1.patientid, e1.Eye, e1.enucleation
FROM EyeData e1
WHERE
    NOT EXISTS(
        SELECT 1
        FROM EyeDate e2
        WHERE
            e2.patientid = e1.patientid AND
            e2.Eye <> e1.Eye
       )
       

>> The HAVING COUNT(..) = 1 is sound logic, until you come across the guy with two left eyes.  <<

The OP stated directly:
"There can be multiple rows for a patient.  There can also be records that have Eye=right and Eye=Left for the same patient."
Presumably that means the same eye could appear multiple times for the same patient (perhaps for different dates?).
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40386010
Select any patients who have only Left eye records, or only Right eye records
SELECT 
      [PersonId],
      max(Enukleation) as Enucleation
 FROM EyeData
GROUP BY PersonId
HAVING ( MIN(eye) = 'Left'  and MAX(eye) = 'Left') 
    OR ( MIN(eye) = 'Right' and MAX(eye) = 'Right') 

Open in new window

0
 

Author Closing Comment

by:soozh
ID: 40386019
Thanks for the feedback.  Pleased to see PortletPaul come in with a solution along my lines... shows i must have been listening to him all the other times he has answered my questions.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40386096
thanks,

btw: Scott Pletcher's approach would also work
0
 

Author Comment

by:soozh
ID: 40386137
yes that is true... maybe i should have given him some points... sorry.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40386286
I can "unaccept" the question for you, if you want.

and, while I'm here, the suggestion I made can be further simplified.

SELECT 
      [PersonId],
      max(Enukleation) as Enucleation
 FROM EyeData
GROUP BY PersonId
HAVING MIN(eye) =  MAX(eye)  -- it's not as obvious, but it has the same effect

Open in new window

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now