Solved

Query syntax

Posted on 2014-10-16
13
168 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 47

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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