laurajs laurajs - 1 year ago 63 Question

Display the table names in a select statement instead of IDs

I have a table (gridview) appearing, once a 'patient' logs in they can see their orders that they have placed (the order table/ Order_pres).

The order table contains a list of foreign keys all from other tables i.e Patient Id from the Patient table and PharmacyId from the pharmacy table:


In the patient/ doctor/ pharmacy tables these all have names - I want my statement to show the names instead of the Ids in the table as the names would make more sense than viewing a code. Order Id would stay the same (as an Id) as it is only part of the Order_pres table.

If Not IsPostBack Then
Dim conn As New System.Data.SqlClient.SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\surgerydb.mdf;Integrated Security=True;Connect Timeout=30")

Dim cmd2string As String = "SELECT * FROM [Order_pres]"
Dim dt As New System.Data.DataTable()
Dim da As New System.Data.SqlClient.SqlDataAdapter(cmd2string, conn)
da.SelectCommand.Parameters.Add("@PatientId", System.Data.SqlDbType.Int).Value = CInt(Session("PatientId").ToString())

GridVieworders.DataSource = dt
End If

OrderId - will stay the same and comes from the order_pres table

PatientId comes from the Patient table and I want to get 'Forename'
and 'Surname' from the table connected to Patient Id.

MedicineId comes from the medicine table and I want to get 'Name'

Pharmacy - Comes from the pharmacy table and I want to get 'pharmname'

Doctor - comes from the Doctor table and I want to get 'Name'

Date ordered - comes from the Order_pres and will stay the same

hopefully someone can help. Kind regards

Answer Source

Perhaps something like this

You'll notice two things.
1) Using a LEFT JOIN in case one or more of the key IDs are not populated

2) Joining to SOMEFIELD, you didn't specify the primary key on the supplemental tables

Select A.OrderID
      ,C.Name      as MedicineName
      ,E.Name as DoctorName
 From  order_pres  A
 Left  Join Patient   B on (A.PatientId  = B.SomeField)
 Left  Join medicine  C on (A.MedicineId = C.SomeField)
 Left  Join pharmacy  D on (A.Pharmacy   = D.SomeField) 
 Left  Join Doctor    E on (A.DoctorID   = E.SomeField)