Query syntax

Hello,

MS SQL Server 2008.

I have two tables:

One of Operations and one for Eyes.

For every row in Operations there are two rows in Eyes (left and right!)

Operation table has:
Pat_id
Operation_date
Approved

Eyes table has
Pat_id
Operation_date
Eye_id (1 or 2)
Visus

Open in new window

I would like to return a dataset that has the following:

Pat_id, Operation_date, Visus_Left, Visus_Right, Approved

Open in new window


Note that visus left is when Eye_id = 1 and visus_right is when Eye_id = 2

Can anyone write me the sql?
soozhCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<air code>

SELECT o.Pat_id, o.Operation_date, e1.Visus as visus_left, e2.Visus as Visus_right, o.Approved
FROM Operation o
   JOIN Eyes e1 ON o.Pat_id = e1.Pat_id AND e1.Eye_id = 1
   JOIN Eyes e2 ON o.Pat_id = e2.Pat_id AND e2.Eye_id = 2

Open in new window

0
PadawanDBAOperational DBACommented:
Is it possible for the same patient to have multiple operations ?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>For every row in Operations there are two rows in Eyes (left and right!)
Might not be a bad idea to run some maintenance code on this every once in awhile...
-- Make sure each o has a single e row with Eye_id = 1
SELECT o.Pat_id, o.Operation_date, COUNT(e.Visus) as left_count
FROM Operation o
   JOIN Eyes e ON o.Pat_id = e.Pat_id AND e.Eye_id = 1
GROUP BY o.Pat_id, o.Operation_date
HAVING COUNT(e.Visus) <> 1

Open in new window


Repeat the above code for the right eye.
0
hnasrCommented:
What is the relation between the operation_date in both tables.
Which date you want in the query?
0
PortletPaulfreelancerCommented:
Assuming it is a combination of pat_id and operation_date the forms a unique record in the operations table
and
that an operation might involve:
left eye only, or
right eye only, or
both eyes (e.g. emergency)

then left join from operation to eye details seems needed, like this:
SELECT
        o.Pat_id
      , o.Operation_date
      , L.Visus AS Visus_left
      , R.Visus AS Visus_right
      , o.Approved
FROM Operation AS O
LEFT JOIN Eyes AS L
        ON o.Pat_id = l.Pat_id
       AND o.Operation_date = l.Operation_date
       AND l.Eye_id = 1
LEFT JOIN Eyes AS R
        ON o.Pat_id = r.Pat_id 
       AND o.Operation_date = r.Operation_date
       AND r.Eye_id = 2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.