MySQL Query; Combine two tables

Question

I have two tables: Invoice

 id number   date      client end client city   vracht
  1   4271   2014-5-28 ALLIN  STIHO      ZWOLLE 0

and Materials.

 id number material thickness length width amount price
  1 14271  Ocoume          10   2500  1220    150   2,3
  2 14271  Ocoume          15   2500  1220     60   2,3
  3 14271  Ocoume          18   2500  1220    125   2,3
  4 14271  Ocoume          22   2500  1220     44   2,3
  5 14271  Ocoume          40   2150  1000     72   2,3
  6 14271  Ocoume          18   3100  1530     25   2,3

In the Invoice table are the invoices. In the Materials table are the materials that belong to the invoices.

What I want is to combine those, which have the same invoice number (number column in the table), with a select-query, like this:

number date      client end client city   vracht material thickness length width amount price
 14271 2014-5-28 ALLIN  STIHO      ZWOLLE      0 Ocoume          10   2500  1220    150   2,3
 14271 2014-5-28 ALLIN  STIHO      ZWOLLE      0 Ocoume          15   2500  1220     60   2,3
 14271 2014-5-28 ALLIN  STIHO      ZWOLLE      0 Ocoume          18   2500  1220    125   2,3
 14271 2014-5-28 ALLIN  STIHO      ZWOLLE      0 Ocoume          22   2500  1220     44   2,3
 14271 2014-5-28 ALLIN  STIHO      ZWOLLE      0 Ocoume          40   2150  1000     72   2,3
 14271 2014-5-28 ALLIN  STIHO      ZWOLLE      0 Ocoume          18   3100  1530     25   2,3

How would this query look?

Answer 1

select 
    `f`.`number`,
    `f`.`client`, 
    `f`.`eind_client`, 
    `f`.`city`, 
    `f`.`vracht`,
        `m`.`material`, 
        `m`.`thickness`, 
        `m`.`length`, 
        `m`.`width`, 
        `m`.`amount`, 
        `m`.`price`
from 
     `invoice` as `f` 
right outer join 
     `materials` as `m`
on 
    `f`.`number`=`m`.`number`

Answer 2

 select * from  invoice, materials where invoice.number=materials.number

Instead of *, it is recommended to write down the required columns..

Answer 3

You can use an JOIN to retrieve the columns from both tables.

example:

SELECT *
FROM
    Invoice AS i,
    Materials AS m,
WHERE
    m.number = i.number
    AND
    m.number = 14271

Answer 4

You can use a join.

  SELECT * FROM Material as M LEFT JOIN Invoice as I ON I.number=M.Number

Answer 5

As mentioned, If you have a foreign key relation you could use JOIN. That should give you the desired outcome.

You could use:

Select "columns you need " or "* "from invoice, materials 
WHERE invoice.number = material.number