Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

Query syntax

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
soozh
Asked:
soozh
  • 3
  • 3
  • 3
  • +3
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
soozhAuthor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Also, there is patientID and patient_ID, which should be the same.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
That seems to work, assuming Enucleation is an integer which represents the left-eye or right-eye.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
PortletPaulCommented:
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
 
soozhAuthor Commented:
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
 
PortletPaulCommented:
thanks,

btw: Scott Pletcher's approach would also work
0
 
soozhAuthor Commented:
yes that is true... maybe i should have given him some points... sorry.
0
 
PortletPaulCommented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now