komplizierte m:n Fall mit outer join

[klausa2]

Eine komplizierte Variante des alten Buch - Autor Problems.

Ein Buch hat 0,1 oder mehrere Autoren
Ein Autor hat 1 oder mehr Bücher
Suchstring soll sowohl in Buch wie Autor gefunden werden und Buch + Autor( evtl. leer) ausgegeben werden.
Das ganz soll noch auf SQLite3 auf einem Smartphone laufen, SQLite3 hat nicht alle SQL Funktionalität: SQL92 minus Right und Full Join, im wesentlichen.

Table book { bookid, title}
Table author {authorid, name}
Table bookauthorrelation {bookid, authorid}

Beispiel:
book
1 U-Bahn London (kein Autor)
2 Londoner Tagebuch
3 Der Holzfäller
4 Wolfsblut
5 Lockruf des Goldes

author
1 Bernd Meier
2 Jack London
3 Horst Esch

bookauthorrelation
2 1 (Buch 2 hat 2 Autoren)
2 3
3 3
4 2 (Autor 2 hat 2 Bücher)
5 2

Wenn ich nun nach "London" suche, möchte ich alle Bücher, bei denen London als Substring im Titel ODER Autor vorkommt, also

Book, author
U-Bahn London, (null)
Londoner Tagebuch, Bernd Meier (der erste der Autoren genügt)
Wolfsblut, Jack London
Lockruf des Goldes, Jack London

Wie kann ich das am einfachsten erreichen? Wenn möglich mit nur einem sql-statemnt, und ohne Erzeugung von temporären tables. SQLite kann einen left outer join.
Notfall auch mit 2 Selects, deren Ergebniss ich dann addieren müsste (aber ohne dabei Duplikate zu erzeugen, bite).

Für Ideen oder Tips schon mal Danke im voraus.

[Falk Prüfer]

Hallo,
sollte doch eigentlich eine einfache OR Abfrage reichen!?

[highlight=sql]
select b.title Book, min(a.name) Author
from book b
left outer join bookauthorrelation ba on ba.bookid = b.bookid
left outer join author a on a.authorid = ba.authorid
where a.name like '%London%'
or b.title like '%London%'
group by b.title Book
[/highlight]

Gruß Falk

[FlexGer]

Also bei mir funktioniert die Abfrage von Falk, nur das "book" beim Group By weg machen.

[highlight=sql]
CREATE TABLE book
(bookid number(12), title varchar2(30));

CREATE TABLE author
(authorid number(12), name varchar2(30));

CREATE TABLE bookauthorrelation
(bookid number(12), authorid number(12));

INSERT INTO book (bookid, title) VALUES (1, 'U-Bahn London');
INSERT INTO book (bookid, title) VALUES (2, 'Londoner Tagebuch');
INSERT INTO book (bookid, title) VALUES (3, 'Der Holzfäller');
INSERT INTO book (bookid, title) VALUES (4, 'Wolfsblut');
INSERT INTO book (bookid, title) VALUES (5, 'Lockruf des Goldes');

INSERT INTO author (authorid, name) VALUES (1, 'Bernd Meier');
INSERT INTO author (authorid, name) VALUES (2, 'Jack London');
INSERT INTO author (authorid, name) VALUES (3, 'Horst Esch');

INSERT INTO bookauthorrelation (bookid, authorid) VALUES (2, 1);
INSERT INTO bookauthorrelation (bookid, authorid) VALUES (2, 3);
INSERT INTO bookauthorrelation (bookid, authorid) VALUES (3, 3);
INSERT INTO bookauthorrelation (bookid, authorid) VALUES (4, 2);
INSERT INTO bookauthorrelation (bookid, authorid) VALUES (5, 2);


SELECT b.title Book, min(a.name) Author
FROM book b
LEFT OUTER JOIN bookauthorrelation ba ON ba.bookid = b.bookid
LEFT OUTER JOIN author a ON a.authorid = ba.authorid
WHERE a.name LIKE '%London%'
OR b.title LIKE '%London%'
GROUP BY b.title;
[/highlight]

[highlight=sql]
BOOK AUTHOR
------------------------------ ------------------------------
Lockruf des Goldes Jack London
Londoner Tagebuch Bernd Meier
U-Bahn London
Wolfsblut Jack London
[/highlight]

[klausa2]

Danke. Auf das zweite join bin ich nicht gekommen.

[klausa2]

So, jetzt gibt es doch noch eine weitere Verkomplizierung.

Es gibt ein weiteres Buch ohne Autor:

book
6 U-Bahn London (kein Autor)

Es hat also den gleiche Titel wie Buch 1 (andere Verlag Vielleicht)

und soll natürlich in der Ausgabe nich weg-aggregiert werden. Die Ausgabe soll dann also so aussehen:

Book, author
U-Bahn London, (null)
U-Bahn London, (null)
Londoner Tagebuch, Bernd Meier (der erste der Autoren genügt)
Wolfsblut, Jack London
Lockruf des Goldes, Jack London

Bei den Büchern mit Autor soll aber wie bisher nur eine Zeile in der Ausgabe stehen.

Wie kann man das auch noch hinkriegen?

[klausa2]

Ich habe es gefunden:

...
GROUP BY b.title || b.bookid

Wenn es also zwei verschiedene Bücher sind, dann werden auch beide ausgegeben.

[fanderlf]

stattdessen würde ich lieber

group by b.title, b.bookid

verwenden. Das Ergebnis sollte auf jeden Fall a, besser sein und b, performanter sein