?
Solved

Query syntax

Posted on 2014-10-16
13
Medium Priority
?
173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +3
13 Comments
 
LVL 66

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 51

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 1000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
LVL 66

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 49

Accepted Solution

by:
PortletPaul earned 1000 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 49

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 49

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

766 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