view with all possible combinations between two tables

mysql, sql

Solution

Here is your query !

 mysql> select a.id as id_a,b.id as id_b from ta a
        -> cross join tb b;
    +------+------+
    | id_a | id_b |
    +------+------+
    |    1 |    1 |
    |    1 |    2 |
    |    2 |    1 |
    |    2 |    2 |
    |    3 |    1 |
    |    3 |    2 |
    |    4 |    1 |
    |    4 |    2 |
    +------+------+
    8 rows in set (0.00 sec)

Problem

I need a view between two tables, basically with the id's and all the possible combinations between them without repeat. The tables and the data: ``` CREATE TABLE `ta` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) INSERT INTO `ta` (`id`, `name`) VALUES (1, 'ta1'); INSERT INTO `ta` (`id`, `name`) VALUES (2, 'ta2'); INSERT INTO `ta` (`id`, `name`) VALUES (3, 'ta3'); INSERT INTO `ta` (`id`, `name`) VALUES (4, 'ta4'); CREATE TABLE `tb` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) INSERT INTO `tb` (`id`, `name`) VALUES (1, 'tb1'); INSERT INTO `tb` (`id`, `name`) VALUES (2, 'tb2'); ``` results I want in the view: ``` view_rel_TAXTB id_a,id_b 1,1 2,1 3,1 4,1 1,2 2,2 3,2 4,2 ```

Original source