Finde einmalige Datensätze im Vergleich mehrerer Tabellen
select version() as 'mysql version';
create table IF NOT EXISTS tabelle1 (id bigint, vorname varchar(20), nachname varchar(20));
insert into tabelle1 (id, vorname, nachname)
values (1, 'Egon', 'Leuchter'),
(2, 'Hans', 'Wurst'),
(3, 'Max', 'Mustermann'),
(4, 'Karl', 'Heinz'),
(5, 'Kai', 'Uwe'),
(6, 'Daniel', 'Düsentrieb');
create table IF NOT EXISTS Tabelle2 (Datum date, Person1 varchar(20), Person2 varchar(20), Person3 varchar(20), Person4 varchar(20), Person5 varchar(20));
insert into tabelle2
values ('26.06.2017', 'Leuchter', 'Wurst', 'Mustermann', 'Heinz', 'Uwe'),
('27.06.2017', 'Mustermann', 'Max', 'Mustermann', 'Heinz', 'Uwe');
create table IF NOT EXISTS Tabelle3 (Datum date, Person1 varchar(20), Person2 varchar(20), Person3 varchar(20), Person4 varchar(20), Person5 varchar(20));
insert into tabelle3
values ('26.06.2017', 'Arm', 'Wurst', 'Mustermann', 'Karl', 'Hans');
select * from tabelle1 where nachname is null or vorname is null;
select * from tabelle2 where person1 is null or person2 is null or person3 is null or person4 is null or person5 is null;
SELECT id, t1.nachname, t2.person1
FROM tabelle1 t1
LEFT JOIN tabelle2 t2
ON t1.nachname = t2.person1
WHERE t2.person1 is null;
SELECT id, t1.nachname, t2.person1
FROM tabelle1 t1
LEFT JOIN tabelle2 t2
ON t1.nachname = t2.person1
AND
t1.vorname = t2.person2
WHERE t2.person1 is null;
drop table tabelle1;
drop table tabelle2;
drop table tabelle3;
|
run
| edit
| history
| help
|
0
|
|
|