如何在MySQL中联接多个表? [英] How to join multiple tables in MySQL?

问题描述

我想看看客户从给定制造商那里订购了什么.

I want to see what customers ordered what from a given manufacture.

我有这些表(带有列):

I have theses tables (with columns):

• 项目(item_num，订单号，库存编号，手册代码，数量等)
• 库存(stock_num， manu_code ，说明，单价等)
• 订单( order_num ，order_date， customer_num ，ship_instruct等)
• 客户( customer_num ，fname，lname，公司，地址1等)

• items (item_num, order_num, stock_num, manu_code, quantity, etc.)
• stock (stock_num, manu_code, description, unit_price, etc.)
• orders (order_num, order_date, customer_num, ship_instruct, etc.)
• customer (customer_num, fname, lname, company, address1, etc.)

这是我现在的查询，但我相信它会返回某种交叉产品:

This is my query right now, but I believe it is returning a cross product of some sort:

SELECT concat(c.fname," ", c.lname) AS fullname, s.description
FROM items i, stock s, customer c JOIN orders o 
ON o.customer_num=c.customer_num 
WHERE o.order_num=i.order_num AND i.manu_code = 'ANZ';

哪个返回一个大列表(1000行)，其中包含大量重复的整体，

Which returns a big list (1000 lines) with lots of duplicate entires,

Anthony Higgens | baseball gloves
Anthony Higgens | baseball gloves
       .                 .
       .                 .
       .                 .
Kim Satifer     | running shoes

我在做什么错了?

推荐答案

尝试一下:

SELECT DISTINCT concat(c.fname," ", c.lname) AS fullname, s.description
FROM customer c 
INNER JOIN orders o ON c.customer_num = o.customer_num
INNER JOIN items i ON o.order_num = i.order_num
INNER JOIN stock s on s.stock_num = i.stock_num
WHERE i.manu_code = 'ANZ'

这篇关于如何在MySQL中联接多个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！