Solved

Query syntax

Posted on 2014-10-16
13
163 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 45

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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:ScottPletcher
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

17 Experts available now in Live!

Get 1:1 Help Now