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

Question

我有三张桌子，分别是处方、预约和付款。处方与预约有关系，预约与付款有关系。目前，我只能从处方表跳转到约会表，并获取aDate字段并在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;         
        }

Answer 1

在你的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 ";

Answer 2

只需将其与payment表连接，就像对另一个表所做的那样。

SELECT prescriptionID, app.aDate,pay.Amount FROM PRESCRIPTION AS pres
 LEFT OUTER JOIN appointment as app on pres.appointmentid = app.appointmentid
 Left outer join Payment as pay on app.appointmentid = pay.appointmentid

Answer 3

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

编辑:这是一个简单的，这就是为什么我们中的三个人正在打击围栏。您需要更多地学习SQL。我建议在添加C#和所有连接类的复杂性之前，找到像SSMS这样的SQL工具来运行查询。SQL Server Management Studio