CODEWITHSUNDEEP
mysqlsqljoin
broinjc
broinjc

Reputation: 2699

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):

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?

Upvotes: 0

Views: 12345

Answers (4)

DWright
DWright

Reputation: 9500

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.

Upvotes: 1

broinjc
broinjc

Reputation: 2699

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

Upvotes: 0

Ajo Thomas
Ajo Thomas

Reputation: 11

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

Upvotes: 0

Marco
Marco

Reputation: 57573

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'

Upvotes: 0

Related Questions