Abfrage über mehrere Tabellen...

[mjenke]

Guten Morgen, alle miteinander!

Nehmen wir mal an, ich hätte eine Paradox7-Datenbank, die ich mit einer Delphi4-Applikation anspreche. Nehmen wir weiter an, in dieser Datenbank gäbe es drei Tabellen:

- tfilm
- tcastcrew
- tdarsteller

Aufbau:

tfilm
-----
Id - Integer
Titel - Alpha (50)

tcastcrew
---------
Id - Integer
Nachname - Alpha (50)
Vorname - Alpha (50)

tdarsteller
-----------
Id - Integer
IdFilm - Integer
IdCast - Integer

Die Tabellen tfilm und tcastcrew beinhalten textliche Informationen, während die Tabelle tdarsteller als Verknüpfungstabelle zwischen den beiden dient.

Mit

SQL-Code:

SELECT DISTINCT t1.Id, t1.Titel FROM
tfilm t1
LEFT JOIN tdarsteller t2 ON t1.Id = t2.IdFilm
LEFT JOIN tcastcrew   t3 ON t3.Id = t2.IdCast
WHERE t3.Vorname + " " + t3.Nachname LIKE "%Harrison Ford%"
ORDER BY Titel

beispielsweise könnte ich aus dieser Datenbank alle Filmtitel heraussuchen, zu denen "Harrison Ford" als Darsteller eingetragen ist.

Jetzt zu meiner eigentlichen Frage: Kann mir jemand auf die Sprünge helfen, wie ich die Abfrage zu formulieren habe, wenn ich mehrere Schauspieler angeben möchte und will, dass sie ALLE in diesem Film mitspielen? Also beispielsweise, dass ich alle Filme heraussuchen möchte, in denen "Harrison Ford" UND "Mark Hamill" mitspielen?

Mit

SQL-Code:

SELECT DISTINCT t1.Id, t1.Titel FROM
tfilm t1
LEFT JOIN tdarsteller t2 ON t1.Id = t2.IdFilm
LEFT JOIN tcastcrew   t3 ON t3.Id = t2.IdCast
WHERE
    t3.Vorname + " " + t3.Nachname LIKE "%Harrison Ford%"
AND t3.Vorname + " " + t3.Nachname LIKE "%Mark Hamill%"
ORDER BY TITEL

funktioniert es nicht, da die Tabelle tdarsteller ja jeweils EINE Verbindung zwischen tfilm und tcastcrew herstellt. Ich müsste aber ja ALLE Verbindungen überprüfen. Zwar liesse sich so etwas programmtechnisch erschlagen, aber den Aufwand möchte ich nicht treiben (macht schliesslich auch alles wieder ein wenig langsamer). Ich würde es gerne mit einer Query schaffen, allerdings reichen meine SQL-Kenntnisse dafür nicht aus...


Matthias

[Smokey]

Da hast du zwei Möglichkeiten.

Wenn es immer eine feste Anzahl an Schauspielern ist und diese gering ist, also zum Beispiel immer 2 wie in deiner Frage, müsstest du nur eine 2te Instanz von deiner Verknüpfungstabelle abfragen.
Also quasi

SQL-Code:

SELECT DISTINCT 
  t1.Id, 
  t1.Titel 
FROM
  tfilm t1
LEFT JOIN tdarsteller t2 ON t1.Id = t2.IdFilm
LEFT JOIN tcastcrew   t3 ON t3.Id = t2.IdCast
LEFT JOIN tcastcrew   t4 ON t4.Id = t2.IdCast
WHERE 
  t3.Vorname + " " + t3.Nachname LIKE "%Harrison Ford%"
AND
  t4.Vorname + " " + t4.Nachname LIKE "%%Mark Hamill%%"
ORDER BY Titel

Oder, wenn du eine variable Anzahl an Schauspielern hast, also zb der User auch mal 10 Schauspieler auswählen kann und somit dein SQL-Statement flexibel sein muss :
Einfach Subquerys für jeden Schauspieler machen, die dir deren Filme zurückgeben, und dann Nach ID's suchen die in allen Subquerrys drin sind. Ich weiss nur nicht ob Paradox IN und Subquerrys unterstützt.

Naja das sind die Wege die mir morgens vor meinem ersten Kaffee einfallen, vielleicht gibts da auch noch bessere )

[mjenke]

Hallo, Smokey!!!

Vielen Dank für diesen Tipp! Ich werde mich vorerst an die erste Variante halten. Da ich die Query ohnehin dynamisch aufbaue, kann ich auch Joins dynamisch gestalten. Mit einem internen Zähler, damit ich nicht mit den Tabellen durcheinanderkomme, sollte das unproblematisch sein.

Super! Vielen Dank nochmal!


Matthias

[Robert_G]

Da du ein Distinct auf t1 abfragst sind weitere Links auf tCastCrew in der FROM Clause unnötig.
Ich habe das gerade in der etwas verspäteten Frühstückspause getippt (einhändig mit dem Brötchen in der linken Hand )

Delphi-Quellcode:

procedure TForm1.PrepareQuery(var Query :TADOQuery; const SearchWords: array of string);
const
  SQL_Body =
    'SELECT Distinct t1.Id' + #10 +
    '               ,t1.Titel' + #10 +
    'FROM   tfilm t1' + #10 +
    '       Left JOIN tdarsteller t2 ON t1.Id = t2.IdFilm' + #10 +
    '       Left JOIN tcastcrew   t3 ON t3.Id = t2.IdCast';

  SQL_Order = 'ORDER  By Titel';

var
  i: Integer;
  Filterstr: string;
begin
  // entferne alle Parameter der ADOQuery
  with Query do
  begin
    Parameters.Clear;

    for i := 0 to high(SearchWords) do
      if Trim(SearchWords[i]) <> '' then
      begin
        FilterStr := FilterStr +
                     Format(' Or' + #10 +
                            '       upper(t3.Vorname || '' '' || t3.Nachname) Like :i_Actor%d'
                            , [succ(i)]);

        // Erzeuge einen Parameter
        with Parameters.AddParameter do
        begin
          DataType := ftString;
          Direction := pdInput;
          Name := 'i_Actor' + IntToStr(succ(i));

          // alle Leerzeichen werden mit % ersetzt
          Value := '%' + AnsiUpperCase(StringReplace(Trim(SearchWords[i])
                                                     , #32, '%'
                                                     , [rfReplaceALL])) + '%';
        end;
      end;
    if FilterStr <> '' then
    begin
      System.Delete(FilterStr, 1, 9);
      FilterStr := 'WHERE' + FilterStr + #10;
    end;
    SQL.Text := SQL_Body + #10 +
                FilterStr +
                SQL_Order;
  end;
end;

Gib ihm eine Query & einen Array aus Suchwörtern und er wird dir die Query so vorbereiten, dass ein "Open" reicht.
Da ich bei mir keine BDE installiert habe habe ich es mit einer ADOQuery getestet (2-3 Änderungen und es läuft mit einer TQuery. )
Eine Suche nach 2 Schauspielern ergibt zum Bleistift (die Parameter sind natürlich bereits besetzt ):

SQL-Code:

SELECT Distinct t1.Id
               ,t1.Titel
FROM   tfilm t1
       Left JOIN tdarsteller t2 ON t1.Id = t2.IdFilm
       Left JOIN tcastcrew   t3 ON t3.Id = t2.IdCast
WHERE  upper(t3.Vorname || ' ' || t3.Nachname) Like :i_Actor1 Or
       upper(t3.Vorname || ' ' || t3.Nachname) Like :i_Actor2
ORDER  By Titel

[Smokey]

Hmmm gibt dein SQL nicht aufgrund des OR filme wo EINER der Schauspieler drin ist ??
Es sollen doch filme ermittelt werden wo ALLE mitspielen.

Oder täusch ich mich da ?

[Robert_G]

Dann nehme AND.
Das SQL Statement ist auch alles andere als optimal (was bei der BDE wahrscheinlich eh nix bringen würde ).
Ich habe eigentlich nur seinen Code genommen und die WHERE Clause rangebastelt.
Ich wollte nur die Zeit in meiner Pause totgeschlagen.

[Smokey]

Hehe und wenn ich AND nehme, brauch ich wieder die zusätzliche Tabelle

Also haste auch noch was für morgen in der Mittagspause 8)

Dachte nur es gibt ne bessere Methode die ich nicht kenne

[Robert_G]

Zitat:

Dann nehme AND

Das war schon schön doof.

Du hast schonn recht. Wenn man eine DB verwendet, die keine Subselects á la "... IN (SELECT ...)" oder "... EXISTS (SELECT ...)" unterstützt, werden die Statements immmer "interessanter" (das war jetzt unter schmerzhafter Selbstbeherrschung ganz höflich ausgedrückt *knirsch* ).

Ich hatte mir das ehrlich gesagt noch nichtmal genau angeschaut, was mJenke da vorhatte.