如何在MySQL中联接多个表?

本文介绍了如何在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'