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
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 ,A.PatientId ,B.Forename ,B.Surname ,A.MedicineId ,C.Name as MedicineName ,A.Pharmacy ,D.pharmname ,A.DoctorID ,E.Name as DoctorName ,A.DateOrderd 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)