join多唯一键表，但只返回一行

cuxqih21  于 2021-06-24  发布在  Mysql
关注(0)|答案(2)|浏览(392)

我的进口清单如下表所示 import_order pk:import_date ,import_no, product_id ```

import_date | import_no | product_id | supplier_id | purchase_cost | qty |

2018-01-02 | M0001 | p00001 | s00001 | 2500 | 7 |
2018-01-02 | M0001 | p00002 | s00001 | 2700 | 4 |
2018-01-02 | M0001 | p00003 | s00001 | 3500 | 5 |

我想加入下表供应商详细信息

supplier_detail

supplier_id | supplier_name | tel |

s00001 | DevelopGlasses | +11254421478 |
s00002 | Operalise | +11252232547 |
s00003 | Azuing | +11245454478 |

我想查询这样的结果

import_date | import_no| product_id | supplier_id| supplier_name| tel |

2018-01-02 | M0001 | p00001 | s00001 |DevelopGlasses|+11254421478 |
2018-01-02 | M0001 | p00002 | s00001 |DevelopGlasses|+11254421478 |
2018-01-02 | M0001 | p00003 | s00001 |DevelopGlasses|+11254421478 |

这是我的sql

SELECT i.,s. FROM import_order i INNER JOIN supplier_detail s ON i.s_id = s.s_id

结果是

import_date | import_no| product_id | supplier_id| supplier_name| tel |

2018-01-02 | M0001 | p00001 | s00001 |DevelopGlasses|+11254421478 |

sqlmysqlinner-join

2条答案

按热度按时间
ht4b089n

ht4b089n1#

你需要纠正你的错误 on 条件为

SELECT i.import_date, i.import_no, i.product_id, 
       i.supplier_id, s.supplier_name, s.tel    
FROM import_order i 
INNER JOIN supplier_detail s 
      ON i.supplier_id = s.supplier_id; -- instead of i.s_id = s.s_id
赞(0）分享  回复(0）举报  2021-06-24
fae0ux8s

fae0ux8s2#

使用左连接、内连接仅选择相关记录

SELECT i.*,s.* FROM import_order i LEFT JOIN supplier_detail s ON i.s_id = s.s_id
赞(0）分享  回复(0）举报  2021-06-24
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com