How to join multiple tables in MySQL?

Question

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 grouping by the item id or whatever you need.

Shomz
– Shomz

2013-01-21 18:25:22 +00:00
Commented Jan 21, 2013 at 18:25 — Shomz
– Shomz, Commented Jan 21, 2013 at 18:25

DWright · Accepted Answer · 2015-06-26 14:10:14Z

1

FROM items i, stock s, customer c does a cartesian join of those three tables involved, but your WHERE does very little restriction on that cartesian join.

If you do some more explicit joining you will grealy cut down on duplicates and more properly express what you are trying to do. Use INNER JOINS and the correct join criteria instead of just listing all the tables after the FROM. Example: (there are more such criteria, you would need to apply to the JOINS, but this is one example): INNER JOIN stock ON stack.manu_code = items.manu_code.

Finally, you can use SELECT DISTINCT or GROUP BY to further reduce duplicates. But if you get your explicit JOINs with JOIN criteria right, you should not have too many duplicates.

edited Jun 26, 2015 at 14:10

answered Jan 21, 2013 at 18:29

DWright

9,5594 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

broinjc Over a year ago

When I do a GROUP BY fullname, it shows what customers ordered 'ANZ' products, but all the descriptions read 'tennis racquet'. how can I concatenate descriptions of products they ordered?

Marco · Accepted Answer · 2013-01-21 19:31:49Z

0

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'

edited Jan 21, 2013 at 19:31

answered Jan 21, 2013 at 18:32

Marco

57.7k15 gold badges135 silver badges159 bronze badges

3 Comments

broinjc Over a year ago

This returned too many rows. I just noticed items and stock both have a stock_num field, and changed last INNER JOIN to: INNER JOIN stock s on s.stock_num = i.stock_num

broinjc Over a year ago

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

Marco Over a year ago

@broinjc: yes, you're right... I didn't notice that field... Thanks

Ajo Thomas · Accepted Answer · 2013-01-22 06:38:31Z

0

This should work :

        SELECT          concat(a.fname, " ", a.lname ) as name
                        , d.description as desc
        FROM            CUSTOMER    a
        INNER JOIN      ORDERS      b
            on          a.customer_num = b.customer_num
        INNER JOIN      ITEMS       c
            on          b.order_num = c.order_num
        INNER JOIN      STOCK       d
            on          c.manu_code = d.manu_code
        where           c.manu_code like 'ANZ'
        group by        name,desc

answered Jan 22, 2013 at 6:38

Ajo Thomas

112 bronze badges

Comments

broinjc · Accepted Answer · 2013-01-22 16:59:36Z

0

Thanks everybody, I think this is working now:

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

answered Jan 22, 2013 at 16:59

broinjc

2,7094 gold badges30 silver badges46 bronze badges

Collectives™ on Stack Overflow

How to join multiple tables in MySQL?

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related