怎么把mysql表连接在一起_如何在MySQL中连接多个表？

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

I have theses tables (with columns):

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';

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

Anthony Higgens | baseball gloves

Anthony Higgens | baseball gloves

. .

. .

. .

Kim Satifer | running shoes

What am I doing wrong?

解决方案

Try this:

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'