如何使用外部联接跳转到表两次以获得我想要的字段

debugcn 发表于 Dev

小马

我有三个表，分别是处方，约会和付款。处方与约会有关系，而约会与付款有关系。目前，我只设法从处方表跳到约会表，并获得aDate字段并将其显示在dategridview中。现在，我需要从处方表跳转到约会表，然后再跳转到付款表以获取金额字段。我该怎么写？

我的DATEGRIDVIEW 在此处输入图片说明

我的桌子在此处输入图片说明

private void prescription_Load(object sender, EventArgs e)
        {
            LoadPrescriptionRecords();
        }

        private void LoadPrescriptionRecords()
        {

            //retrieve connection information info from App.config
            string strConnectionString = ConfigurationManager.ConnectionStrings["SACPConnection"].ConnectionString;
            //STEP 1: Create connection
            SqlConnection myConnect = new SqlConnection(strConnectionString);
            //STEP 2: Create command

            //string strCommandText = "SELECT prescriptionID, app.aDate FROM PRESCRIPTION AS pres";
            string strCommandText = "SELECT prescriptionID, app.aDate FROM PRESCRIPTION AS pres";
            strCommandText += " LEFT OUTER JOIN appointment as app on pres.appointmentid = app.appointmentid";

            myConnect.Open();

            PrescriptionAdapter = new SqlDataAdapter(strCommandText, myConnect);

            //readMEDICATION.Close();
            myConnect.Close();      

            //command builder generates Select, update, delete and insert SQL
            // statements for MedicalCentreAdapter
            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(PrescriptionAdapter);
            // Empty Employee Table first
            Prescription.Clear();
            // Fill Employee Table with data retrieved by data adapter
            // using SELECT statement
            PrescriptionAdapter.Fill(Prescription);

            // if there are records, bind to Grid view & display
            if (Prescription.Rows.Count > 0)
                grdPrescription.DataSource = Prescription;         
        }

петер.петров

您strCommandText只需再加入一个即可，即加入预约付款。

string strCommandText = "SELECT prescriptionID, app.aDate, p.amount FROM PRESCRIPTION AS pres ";
strCommandText += "LEFT OUTER JOIN appointment as app on pres.appointmentid = app.appointmentid ";
strCommandText += "LEFT OUTER JOIN payment p on app.appointmentid = p.appointmentid ";

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。