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): items (item_num, order_num, stock_num, manu_code, quantity, etc.) stock (s

Solution 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.

Solution 2:

Try this:

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

Solution 3:

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

Solution 4:

Thanks everybody, I think this is working now:

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