История изменений
Исправление thesis, (текущая версия) :
Ну сходу вот так:
select * from a;
+----+------------+
| id | name |
+----+------------+
| 1 | ivanov |
| 2 | petrov |
| 3 | sidorov |
| 4 | efimov |
| 5 | rabinovich |
| 6 | smith |
| 7 | obama |
+----+------------+
select * from b;
+----+------+------+
| id | id1 | id2 |
+----+------+------+
| 1 | 1 | 2 |
| 2 | 2 | 4 |
| 3 | 4 | 7 |
| 4 | 6 | 7 |
| 5 | 7 | 1 |
| 6 | 1 | 7 |
+----+------+------+
select a.id, a.name, b.id2 from a left join b on a.id=b.id1 join a as aa on a.id=aa.id;
+----+------------+------+
| id | name | id2 |
+----+------------+------+
| 1 | ivanov | 2 |
| 2 | petrov | 4 |
| 4 | efimov | 7 |
| 6 | smith | 7 |
| 7 | obama | 1 |
| 1 | ivanov | 7 |
| 3 | sidorov | NULL |
| 5 | rabinovich | NULL |
+----+------------+------+
Оно? А как выбрать так, чтобы сразу вставить - не знаю :) Можно проверку целостности отключить, наверное, в смысле, объявить ключи после вставки данных.
Исправление thesis, :
Ну сходу вот так:
select * from a;
+----+------------+
| id | name |
+----+------------+
| 1 | ivanov |
| 2 | petrov |
| 3 | sidorov |
| 4 | efimov |
| 5 | rabinovich |
| 6 | smith |
| 7 | obama |
+----+------------+
select * from b;
+----+------+------+
| id | id1 | id2 |
+----+------+------+
| 1 | 1 | 2 |
| 2 | 2 | 4 |
| 3 | 4 | 7 |
| 4 | 6 | 7 |
| 5 | 7 | 1 |
| 6 | 1 | 7 |
+----+------+------+
select a.id, a.name, b.id2 from a left join b on a.id=b.id1 join a as aa on a.id=aa.id;
+----+------------+------+
| id | name | id2 |
+----+------------+------+
| 1 | ivanov | 2 |
| 2 | petrov | 4 |
| 4 | efimov | 7 |
| 6 | smith | 7 |
| 7 | obama | 1 |
| 1 | ivanov | 7 |
| 3 | sidorov | NULL |
| 5 | rabinovich | NULL |
+----+------------+------+
Оно? А как выбрать так, чтобы сразу вставить - не знаю :)
Исходная версия thesis, :
Ну сходу вот так:
select * from a;
+----+------------+
| id | name |
+----+------------+
| 1 | ivanov |
| 2 | petrov |
| 3 | sidorov |
| 4 | efimov |
| 5 | rabinovich |
| 6 | smith |
| 7 | obama |
+----+------------+
select * from b;
+----+------+------+
| id | id1 | id2 |
+----+------+------+
| 1 | 1 | 2 |
| 2 | 2 | 4 |
| 3 | 4 | 7 |
| 4 | 6 | 7 |
| 5 | 7 | 1 |
+----+------+------+
select a.id, a.name, b.id2 from a left join b on a.id=b.id1 join a as aa on a.id=aa.id;
+----+------------+------+
| id | name | id2 |
+----+------------+------+
| 1 | ivanov | 2 |
| 2 | petrov | 4 |
| 4 | efimov | 7 |
| 6 | smith | 7 |
| 7 | obama | 1 |
| 3 | sidorov | NULL |
| 5 | rabinovich | NULL |
+----+------------+------+
Оно?