Combine 2 tables in MySQL and create a new one

Question

I have two tables in MySQL:

Table 1

age code
20  ABC
15  DEF
32  ABC
45  DEF

Table 2

geo       code
POLYGON   ABC
POLYGON   DEF
POLYGON   GHI

I counted the double registers with the same "code" on table 1

SELECT code, COUNT(code) AS cnt FROM table1 GROUP BY code HAVING (cnt > 0)

resulted:

code  cnt
ABC    2
DEF    2

I want to combine this result with Table 2 and create a third table:

Table 3

geo      code   cnt
POLYGON  ABC     2
POLYGON  DEF     2
POLYGON  GHI     0

Is that possible?

Try INSERT INTO <new table> SELECT (<your JOIN query>) ?

Maximus2012
– Maximus2012

2015-04-10 16:42:14 +00:00
Commented Apr 10, 2015 at 16:42 — Maximus2012
– Maximus2012, Commented Apr 10, 2015 at 16:42
And before that you have to create <new table>

hellcode
– hellcode

2015-04-10 16:43:01 +00:00
Commented Apr 10, 2015 at 16:43 — hellcode
– hellcode, Commented Apr 10, 2015 at 16:43
no, you dont. you can do it all at once.

pala_
– pala_

2015-04-10 16:59:28 +00:00
Commented Apr 10, 2015 at 16:59 — pala_
– pala_, Commented Apr 10, 2015 at 16:59

pala_ · Accepted Answer · 2015-04-10 17:03:26Z

2

You can create a table from the output of a query, by using create table <tablename> as <query> syntax.

So all we really have to do now is craft a query that returns the results you want. Here is one such query:

select geo, t2.code, ifnull(cnt, 0) cnt
  from table2 t2
    left join
      (select code, count(*) cnt
         from table1
         group by code
       ) q1
     on t2.code = q1.code

demo fiddle

Then all we have to do is plug that into our create table statement.

create table new_table as
select geo, t2.code, ifnull(cnt, 0) cnt
  from table2 t2
    left join
      (select code, count(*) cnt
         from table1
         group by code
       ) q1
     on t2.code = q1.code;

and here's a bonus query that gets exactly the same results:

select t2.geo, t2.code, count(t1.code) cnt
  from table2 t2
    left join table1 t1
      on t1.code = t2.code
  group by t2.code;

updated fiddle

edited Apr 10, 2015 at 17:03

answered Apr 10, 2015 at 16:55

pala_

9,0101 gold badge18 silver badges32 bronze badges

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

1 Comment

Seph1603 Over a year ago

Now if I want to be sure if the table "new_table" exists or not to create it. It would be: DROP TABLE IF EXISTS new table;

Collectives™ on Stack Overflow

Combine 2 tables in MySQL and create a new one

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related