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?

@MZeinstra You could always start with the documentation: http://dev.mysql.com/doc/refman/5.0/en/join.html — shree.pat18, Dec 03 '14 at 09:18
@Aditya No there isn't a a key relation. @Harry nothing is linked, except the `number` column, but those do not have a relation. — M Zeinstra, Dec 03 '14 at 09:20
best practice would be to link the tables, so material id column can sit in invoices. and then you can perform a join. — Harry, Dec 03 '14 at 09:21
@MZeinstra you should learn the joins for that..well I have written the query that will perform your required task. — Danyal Sandeelo, Dec 03 '14 at 09:27

score 3 · Answer 1 · edited Dec 03 '14 at 09:48

3

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

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

edited Dec 03 '14 at 09:48

Persixty

8,165
2
13
35

answered Dec 03 '14 at 09:21

Danyal Sandeelo

12,196
10
47
78

1

You should use the JOIN keyword along with the type of join rather than this style. – shree.pat18 Dec 03 '14 at 09:22
@shree.pat18 this is basically inner join, if you want to write the word inner join, it will be like .. select * from invoice inner join materials on invoice.number=materials.number – Danyal Sandeelo Dec 03 '14 at 09:23
There was a single character typo but the site requires a minimum of 6 character edits. Shish! – Persixty Dec 03 '14 at 09:24

score 3 · Accepted Answer · edited Dec 03 '14 at 12:38

3

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`

edited Dec 03 '14 at 12:38

M Zeinstra

1,931
4
17
46

answered Dec 03 '14 at 09:25

starko

1,150
11
26

This works good, but not perfect.. Every record of `materials` shows up 6 times.. Do you know how to fix this? – M Zeinstra Dec 03 '14 at 09:38
1

Maybe try instead join use left(or right) outer join. – starko Dec 03 '14 at 09:40
Okay, right join works.. I've also added group by, so if you accept my edit, I can accept your answer :) – M Zeinstra Dec 03 '14 at 09:57
Sorry, this edit was wrong.. I don't need Group by.. Please accept my edit :) – M Zeinstra Dec 03 '14 at 10:31

score 1 · Answer 3 · answered Dec 03 '14 at 09:22

1

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

answered Dec 03 '14 at 09:22

J A

1,776
1
12
13

@DanyalSandeelo: Have read the question? From the table 3, I don't see any other number than 14271. – J A Dec 03 '14 at 09:27
This is a silly topic to argue about. The OP didn't ask for all the records. And this is simple enough query to understand and modify. – J A Dec 03 '14 at 09:31

score 1 · Answer 4 · answered Dec 03 '14 at 09:23

1

You can use a join.

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

answered Dec 03 '14 at 09:23

Naved Munshi

487
1
5
17

Harry · Answer 5 · 2014-12-03T09:57:27.560

1

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

edited Dec 03 '14 at 09:57

answered Dec 03 '14 at 09:24

Harry

3,031
7
42
67

That is not how UNION works. UNION will combine result sets i.e. Set 1 followed by Set 2. It will not append columns from Set 2 to the columns of Set 1, which is what OP wants. – shree.pat18 Dec 03 '14 at 09:29
No. Take a look at this: http://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union – shree.pat18 Dec 03 '14 at 09:36

MySQL Query; Combine two tables

5 Answers5