Solved

list data from 2 tables

Posted on 2008-10-10
4
213 Views
Last Modified: 2012-05-05
i am a novice to sql and oracle express edition i am learning . i need to display patient 's number(pno) , patient name(pname) and drug name (dname) they have been prescribed to .

i have tried: select pno, pname from patient but it is only for a single table drug name (dname) should be associated
hospital-data.doc
0
Comment
Question by:evry2004
  • 2
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22689791
can you show the 2 tables full structure? and the column that is the foreign key?
usually, this should be a 3-tables design:

TABLE: patient
p_no
p_name
comments
...


TABLE: drug
d_no
d_name
comments
...

TABLE: prescription
p_id
p_no  => patient.p_no
d_no  => drug.d_no
date_start
date_end
comments
...

0
 

Author Comment

by:evry2004
ID: 22694759
tables are attached
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22694884
prescription = dose :)

this will be the base SQL...
you might need to add more conditions...
select *

  from patient p

  join dose 

    on dose.pno = p.pno 

  join drug d

    on d.dno = dose.dno

Open in new window

0
 

Author Closing Comment

by:evry2004
ID: 31505150
thank you
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

867 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

18 Experts available now in Live!

Get 1:1 Help Now