如何使用where条件连接两个表并显示记录 - how to join two tables with a where condition and display the records

所以我很难在Stack上找到一个好的答案。我希望运行一个将两个表上的信息组合在一起的查询。到目前为止，这就是我所拥有的。实际情况如下：

为了实现这一点，我将尝试从我这边解释更多：

我有两张桌子：

Comparitive_st_sup
___________________
id  | tender_id | item_name | slno | supplier_name | prod_description
________________________________________________________________________
1       401        Collinear   1      OnlineMetals    Description comes here    
2       401        Filter      2      OnlineMetals    Description comes here
3       401        Antenna     3      OnlineMetals    Description Comes here
4       455        Primer      1      KR Electronics  Description comes here
5       455        Chock       2      KR Electronics  Description comes here

comparitive_st_tech_compliance
_______________________________
id | tender_id | item_name  | slno | supplier_name  | tech_analyst_comment
__________________________________________________________________________
1      401        Collinear    1      OnlineMetals     90%
2      401        Filter       2      OnlineMetals     25%
3      401        Antenna      3      OnlineMetals     87%
4      455        Primer       1      KR Electronics   64%
5      455        Chick        2      KR Electronics   80%
Now i am expecting a result like:
401    Collinear     1    OnlineMetals    Description comes here   90%
401    Filter        2    OnlineMetals    Description comes here   25%
401    Antenna       3    OnlineMetals    Description comes here   87%

根据所选的tender_id，该值将作为查询字符串传递，并且必须相应地显示记录。感谢帮助。。

我试过这个，但结果不正确：

 Select comparitive_st_sup.*,
  comparitive_st_sup.tender_id As tender_id1,
  comparitive_st_sup.supplier_name As supplier_name1,
  comparitive_st_sup.slno As slno1,
  comparitive_st_sup.prod_description As prod_description1,
  comparitive_st_sup.item_name As item_name1,
  comparitive_st_sup.total As total1,
  comparitive_st_tech_compliance.tech_analyst_comment
  From comparitive_st_tech_compliance
  Right Join comparitive_st_sup On comparitive_st_sup.tender_id =
  comparitive_st_tech_compliance.tender_id
  Where comparitive_st_sup.tender_id = 401

我需要显示comparative_strongup中的所有字段，并且只显示一个带有where条件tender_id的字段（techn_analyst_comment）。现在记录正在复制。它显示的不是3条记录，而是9条记录。我犯了什么错误吗？

如果您不想要或不能GROUP BY，可以尝试子查询。您也可以在子查询中ORDER BY日期/id。

SELECT
    cs.*,
    cs.tender_id AS tender_id1,
    cs.supplier_name AS supplier_name1,
    cs.slno AS slno1,
    cs.prod_description AS prod_description1,
    cs.item_name AS item_name1,
    cs.total AS total1,
    (
        SELECT
            ct.tech_analyst_comment
        FROM comparitive_st_tech_compliance AS ct
        WHERE ct.tender_id = cs.tender_id
        LIMIT 1
    ) AS tech_analyst_comment
FROM comparitive_st_sup AS cs
WHERE cs.tender_id = 401

LE:IF和ONLY如果slno在两个表（comparitive_st_sup.slno = comparitive_st_tech_compliance.slno）中都是相同的标识符，那么您可以使用AND comparitive_st_sup.slno = comparitive_st_tech_compliance.slno的额外联接参数来联接它们，因此您的初始查询将如下所示：

Select comparitive_st_sup.*,
  comparitive_st_sup.tender_id As tender_id1,
  comparitive_st_sup.supplier_name As supplier_name1,
  comparitive_st_sup.slno As slno1,
  comparitive_st_sup.prod_description As prod_description1,
  comparitive_st_sup.item_name As item_name1,
  comparitive_st_sup.total As total1,
  comparitive_st_tech_compliance.tech_analyst_comment
From comparitive_st_tech_compliance
Right Join comparitive_st_sup On
    comparitive_st_sup.tender_id = comparitive_st_tech_compliance.tender_id AND
    comparitive_st_sup.slno = comparitive_st_tech_compliance.slno
Where comparitive_st_sup.tender_id = 401

但是，如果表*_strongup和*_tech_compliance中的slno不同，则需要在tender_id 旁边添加产品和注释之间的关系

comparitive_st_tech_compliance
+-----------------------------------------------------------------------------------------+
| id | tender_id | product_id | item_name  | slno | supplier_name  | tech_analyst_comment |
+-----------------------------------------------------------------------------------------+
|  1 | 401       |     1      | Collinear  |   1  | OnlineMetals   | description          |
+-----------------------------------------------------------------------------------------+

其中comparative_st_tech_compliance.product_id是Comparative_strongup.id，这也让我建议您更改数据库架构（结构）

附带说明：因此，从您的数据库结构来看，需要指出的一点是它设计得很糟糕。两个选项卡中都有重复的字段，因此如果需要更新ex.supplier_name，则需要更新所有表。现在，假设您愿意进行更改，我建议将您的数据拆分为3个表，而不考虑slno可能是这两个表之间的标识符。

comparative_supplier
+---------------------+
| id | supplier_name  |
+---------------------+
| 1  | OnlineMetals   |
| 2  | KR Electronics |
+---------------------+
comparitive_st_sup
+--------------------------------------------------------------------+
| id | tender_id | supplier_id | slno | item_name | prod_description |
+--------------------------------------------------------------------+
| 1  | 401       |      1      |   1  | Collinear | description      |
| 2  | 401       |      1      |   2  | Filter    | description      |
| 3  | 401       |      1      |   3  | Antenna   | description      |
| 4  | 455       |      2      |   1  | Primer    | description      |
| 5  | 455       |      2      |   2  | Chock     | description      |
+--------------------------------------------------------------------+
comparitive_st_tech_compliance
+-----------------------------------------+
| id   | id_supply | tech_analyst_comment |
+-----------------------------------------+
| 15   |     1     |         90%          |
| 56   |     2     |         25%          |
| 123  |     3     |         87%          |
| 412  |     4     |         64%          |
| 684  |     5     |         80%          |
+-----------------------------------------+

有了这种表结构，您可以轻松地联接表，而不需要重复条目和更改字段，而无需更新所有表。

SELECT
    cs.tender_id, sn.supplier_name, cs.slno, cs.item_name,
    cs.prod_description, ct.tech_analyst_comment
FROM comparitive_st_sup AS cs
LEFT JOIN comparative_supplier AS sn ON sn.id = cs.supplier_id
LEFT JOIN comparitive_st_tech_compliance AS ct ON ct.id_supply = cs.id
WHERE cs.tender_id = 401

或者只需更改strongup表并包含技术注释，因为这两个表只在技术注释和产品描述

方面有所不同

根据您的评论，我认为comparative_st_tech_compliance对于单个tender_id在comparative_strongup表中有多行。如果是这样，那么它将返回多行。不管您使用哪个Join。

您可能需要在这里这样做：

Select comparitive_st_sup.*,
  comparitive_st_sup.tender_id As tender_id1,
  comparitive_st_sup.supplier_name As supplier_name1,
  comparitive_st_sup.slno As slno1,
  comparitive_st_sup.prod_description As prod_description1,
  comparitive_st_sup.item_name As item_name1,
  comparitive_st_sup.total As total1,
  comparitive_st_tech_compliance.tech_analyst_comment
  From comparitive_st_tech_compliance
  Where comparitive_st_sup.tender_id = 401  AND comparitive_st_tech_compliance.tender_id =  tender_id1

SELECT comparitive_st_sup.*,
  comparitive_st_sup.tender_id As tender_id1,
  comparitive_st_sup.supplier_name As supplier_name1,
  comparitive_st_sup.slno As slno1,
  comparitive_st_sup.prod_description As prod_description1,
  comparitive_st_sup.item_name As item_name1,
  comparitive_st_sup.total As total1,
  comparitive_st_tech_compliance.tech_analyst_comment
  FROM comparitive_st_sup, comparitive_st_tech_compliance
  WHERE comparitive_st_sup.tender_id = 401
  AND comparitive_st_tech_compliance.tender_id = comparitive_st_sup.tender_id
  GROUP BY comparitive_st_sup.tender_id;