CODEWITHSUNDEEP
phpmysqlsql
Daniel Jensen
Daniel Jensen

Reputation: 141

Select from two tables with same columns

I have to tables, each with 4 columns (id, name, games, points). The column-names are the same in both tables. I would like to select everything from both columns in one big table (like if in soccer, you joined the MLS and Premier League in one table). I've tried this code:

SELECT * FROM league1 JOIN league 2;

but it wasn't the output I wanted. I can see my tables aren't created exatly the same:

CREATE TABLE `league1` (
 `id` int(11) DEFAULT NULL,
 `Navn` varchar(50) COLLATE utf8_danish_ci NOT NULL,
 `kampe` int(11) DEFAULT NULL,
 `Point` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci ROW_FORMAT=DYNAMIC

CREATE TABLE `league2` (
 `id` int(11) DEFAULT NULL,
 `Navn` varchar(50) DEFAULT NULL,
 `kampe` int(11) DEFAULT NULL,
 `Point` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Isn't it possible to join them under the same columns, so I still have the 4 columns but all the rows from both tables?

Upvotes: 1

Views: 97

Answers (2)

Stefano Zanini
Stefano Zanini

Reputation: 5916

You don't want to join but to unite the content of the two tables

SELECT  *
FROM    league1 
UNION ALL
SELECT  *
FROM    league2;

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Use union all to keep duplicates. And in case the number of field are different you can just declare the field matching on both tables instead of *

  SELECT * FROM league1 
  UNION ALL
  SELECT * FROM league2

Upvotes: 1

Related Questions