doushao8421 2015-11-09 09:55
浏览 390
已采纳

如何使用where条件连接两个表并显示记录

So I had a hard time finding a good answer on Stack. I'm looking to run a query that combines the information on two tables together. So far this is what i have got. The actual scenario is as follows:

I will try to explain a bit more from my side to achieve this:

I have two tables :

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%

As per the tender_id selected the value is passing as a querystring and the records has to be displayed accordingly. Help appreciated..

I tried this, but result is not proper:

 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     

I need to display all the fields from comparitive_st_sup and only one field (tech_analyst_comment) with a where condition tender_id. Now the records are duplicating. Instead of 3 records to show, it is showing 9 records. Am i making any mistake?

  • 写回答

4条回答 默认 最新

  • dpwdldgn43486 2015-11-09 11:07
    关注

    If you don't want or can't GROUP BY, you could try a sub-query. You can also ORDER BY date/id in the sub-query.

    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 and ONLY IF slno is the same identifier in both of your tables ( comparitive_st_sup.slno = comparitive_st_tech_compliance.slno ), than you can join them with the extra join argument of AND comparitive_st_sup.slno = comparitive_st_tech_compliance.slno so your initial query will look something like this:

    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
    

    but if slno differs in table *_st_sup and *_tech_compliance than you will need to add a relationship between the products and comments beside the 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          |
    +-----------------------------------------------------------------------------------------+
    

    where comparitive_st_tech_compliance.product_id is Comparitive_st_sup.id, which also brings me to the point where I would suggest you to change your database schema ( structure )

    Side note: So from your database structure, one thing to point out is that it's poorly designed. You have duplicate fields in both tabels, so if you need to update for ex. supplier_name you will need to update all of your tables. Now assuming you are willing to make the changes, I would suggest to split up your data into 3 tables, not taking into account that slno may be an identifier between the 2 tables.

    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%          |
    +-----------------------------------------+
    

    with this table structure you can easily join your tables without duplicate entries and change fields without the need to update all your tables.

    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
    

    or just change your st_sup table and include the the tech comment, because the 2 tables only differ by the tech comment and product description

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题?

悬赏问题

  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 spring后端vue前端
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题