mysql - C#/SQL 连接问题

Question

我有两张 table 。

Table 1: supportstatus

clientid
company
status

Table 2: client
clientid
company
status
state

我还使用一个包含 2 列的表格来分隔 GridView 结果。我目前正在尝试进行两个查询，这些查询将返回一个表列中具有禁用状态的客户端以及另一表列中的相应状态。任何有关解释的帮助将不胜感激。

protected void queryClientCompanyName()
{
    string MyConString = "Driver={MySQL ODBC 5.1 Driver};Server=**;Database=support;User=**; Password=**;Option=3;";
    string queryString = "SELECT company FROM supportstatus WHERE status = 'disabled' Order By company";
    OdbcConnection conn = new OdbcConnection(MyConString);
    conn.Open();
    OdbcCommand command = new OdbcCommand(queryString, conn);
    OdbcDataReader reader = command.ExecuteReader();
    populateGrid(reader, GridView1);
    conn.Close();
    RecordCountLabel.Text = GridView1.Rows.Count.ToString();
}

protected void queryClientCompanyState()
{
    string MyConString = "Driver={MySQL ODBC 5.1 Driver};Server=**;Database=support;User=**; Password=**;Option=3;";
    string queryString = "SELECT cl.state FROM clients cl, supportstatus su WHERE cl.clientid = su.clientid  AND su.status='disabled' Order By cl.company";
    OdbcConnection conn = new OdbcConnection(MyConString);
    conn.Open();
    OdbcCommand command = new OdbcCommand(queryString, conn);
    OdbcDataReader reader = command.ExecuteReader();
    populateGrid(reader, GridView2);
    conn.Close();
}

Answer 1

您应该只需要以下查询来替换您的两个查询:

SELECT su.Company
   , cl.state
FROM clients cl
INNER JOIN supportstatus su ON cl.clientId = su.clientId
WHERE su.status = 'disabled'
Order By cl.company

您希望一个查询能够返回这两个值，以便您知道它们是相关的。正因为如此，您希望将表连接在一起。我刚刚进行了快速谷歌搜索，然后 W3schools有基本的加入信息，techrepublic也有这些应该可以帮助您了解查询数据库的基本概念。