[Blockade] Passendes SQL Statement gesucht

[Mithrandir]

Hi ihr,

ich stecke gerade vor dem Problem, etwas möglichst elegant lösen zu wollen, am Liebsten nur mit SQL.

Ich habe 3 Tabellen:

TURNOVER
TAGS
TAG_RELATIONSHIPS

Aufbau wie folgt:

SQL-Code:

CREATE TABLE TURNOVER (ID INTEGER, EXP_TYPE INTEGER, EXP_VALUE FLOAT, EXP_CATEGORY INTEGER, EXP_COMMENT CHAR(255) CHARACTER SET NONE, EXP_USER INTEGER, EXP_DATE DATE, EXP_LUX INTEGER);
    CREATE TABLE TAGS (ID INTEGER, TERM_NAME CHAR(255) CHARACTER SET NONE);
    CREATE TABLE TAG_RELATIONSHIPS (ID INTEGER, TERM_ID INTEGER, ENTRY_ID INTEGER);

In TURNOVER stehen Umsätze, in TAGS sind alle möglichen Tags aufgelistet, die der User je eingegeben hat und in TAG_RELATIONSHIPS schließlich wird die Verbindung zwischen den beiden Datenbanken geschaffen, indem jedem Eintrag in TURNOVER ein oder mehrere TAGS zugeordnet werden.

Nun möchte ich eine Liste aller Tags haben. Allerdings gefiltert. Wenn ich den Zeitraum 1.3.2009 - 1.4.2009 angebe, dann möchte ich nur die Tags haben, die auch in Einträgen in diesem Zeitraum vorkommen. Außerdem möchte ich die Summe aller Umsätze in diesem Zeitraum pro Tag (also, nicht Tag im Sinne von Wochentag, sondern "Täg" :mrgreen: ).

Bislang mache ich es manuell: Zuerst alle Tags auslesen, dann alle Einträge, und dann die, die nicht passen, verwerfen. Das passiert in Delphi. Könnte man vielleicht auch eine SQL-Abfrage formulieren, die genau das macht? Ich kanns irgendwie nicht... :gruebel:

[jfheins]

Ich hab das jetzt nicht ausgiebig getestet (hab 1 Tag vor der Matheklausur nicht das bedürfnis, einen größeren Test-Datenbestand auszudenken ^^)

Aber ich dachte da an sowas:

SQL-Code:

SELECT * , sum( revenue ) 
FROM TAGS
JOIN TAG_RELATIONSHIPS ON term_id = TAGS.id
JOIN TURNOVER ON entry_id = TURNOVER.id
AND date
BETWEEN 1 
AND 5 
GROUP BY TAGS.id

;)

[Jürgen Thomas]

Hallo,

mein erster Versuch würde so lauten:

SQL-Code:

select Tags.ID, Tags.Term_Name, SUM(exp_value)
  from Tags
  left join Tag_Relationship rel
    on re.entry_id = Tags.ID
  join Turnover to
    on to.ID = rel.Term_ID
 where to.exp_date between :startdate and :enddate
 group by Tags.ID, Tags.Term_Name

Wichtig ist (wie auch bei der Lösung von jfheins), dass alle drei Tabellen per JOIN richtig verknüpft werden.

Ob LEFT JOIN an dieser Stelle korrekt ist, musst du ausprobieren. Möglicherweise sind auch beide JOINs sinnvoll in Klammern zu setzen.

Vielleicht hilft dir die Erläuterung unter Einführung in SQL: Mehrere Tabellen zum genauen Verständnis.

Gruß Jürgen

/Edit
Welche IDs zusammengehören, musst du wissen. Ich habe es andersherum verstanden als jfheins.

[nahpets]

Hallo,

ein dritter Vorschlag:

habe kein Firebird, daher hier mal nur so hingeschrieben:

Folgende Annahme:

TAG_RELATIONSHIPS.TERM_ID gehört zu TAGS.ID
TAG_RELATIONSHIPS.ENTRY_ID gehört zu TURNOVER.ID

SQL-Code:

select *
from  TURNOVER, TAGS, TAG_RELATIONSHIPS
where TURNOVER.ID = TAG_RELATIONSHIPS.ENTRY_ID
and   TAG_RELATIONSHIPS.TERM_ID = TAGS.ID

Damit müssten die drei Tabellen erstmal vollständig zusammengestellt sein.

Nun kommen die Einschränkungen hinzu:

SQL-Code:

select *
from  TURNOVER, TAGS, TAG_RELATIONSHIPS
where TURNOVER.ID = TAG_RELATIONSHIPS.ENTRY_ID
and   TAG_RELATIONSHIPS.TERM_ID = TAGS.ID
and   TURNOVER.EXP_DATE between '1.3.2009' and '1.4.2009' /* <-- kennt Firebird das? */

and   TURNOVER.EXP_DATE >= '1.3.2009' /* <-- ansonsten so */
and   TURNOVER.EXP_DATE <= '1.4.2009'

Nun müssen wir uns noch auf die auszugebenden Spalten einigen:

SQL-Code:

select
  TAGS.ID,
  TAGS.TERM_NAME,         /* <-- Nun möchte ich eine Liste aller Tags haben. */
  Sum(TURNOVER.EXP_VALUE) /* <-- Außerdem möchte ich die Summe aller Umsätze */
from  TURNOVER, TAGS, TAG_RELATIONSHIPS
where TURNOVER.ID = TAG_RELATIONSHIPS.ENTRY_ID
and   TAG_RELATIONSHIPS.TERM_ID = TAGS.ID
and   TURNOVER.EXP_DATE >= '1.3.2009' /* <-- Allerdings gefiltert. */
and   TURNOVER.EXP_DATE <= '1.4.2009' /* <-- Wenn ich den Zeitraum 1.3.2009 - 1.4.2009 angebe */
group by
  TAGS.ID,   /* <-- also, nicht Tag im Sinne von Wochentag, sondern "Täg" */
  TAGS.TERM_NAME  /* (alles, was nicht summiert wird, muss ins Group By) */

Schaumal, ob's Dir weiterhilft. Frei nach dem Motto: Viele Wege führen nach Rom ;-)

[Mithrandir]

:firejump:

Danke ihr drei. :)

@Jürgen: So wie es Stephan und MoD verstanden haben, ist es richtig. :)

Allerdings hapert es noch ein wenig bei dem SUM. So bekomme ich ja die Summe über die komplette Spalte, wenn ich das so wie im letzten Beitrag umsetze (übrigens, danke für die Erklärung, Stephan. :) ). Ich möchte ja nun aber die Summe pro Täg haben... :gruebel:

[DeddyH]

Dann muss der Tag mit in die Ergebnisliste und die Gruppierungsklausel.

[nahpets]

Hallo,

Zitat:

Zitat von Daniel G

:firejump:

Danke ihr drei. :)

@Jürgen: So wie es Stephan und MoD verstanden haben, ist es richtig. :)

Allerdings hapert es noch ein wenig bei dem SUM. So bekomme ich ja die Summe über die komplette Spalte, wenn ich das so wie im letzten Beitrag umsetze (übrigens, danke für die Erklärung, Stephan. :) ). Ich möchte ja nun aber die Summe pro Täg haben... :gruebel:

dann habe ich die Definition von Täg wohl noch nicht verstanden, was bitte meinst Du genau (eventuell mal ein Beispiel, wie das Ergebnis aussehen soll, hier reinstellen).
Meinst Du mit Tag einen Eintrag aus der Tabelle TAGS (also eine Zeile, was nach meinem bisherigen Verständnis einem TERM_NAME entspricht) oder meinst Du einen Kalendertag?

[Mithrandir]

Nee,

ich meine schon einen TÄG (Obst, Gemüse, Süßigkeiten, Wasser). Beispielausgabe ist im Anhang, ich habe das SQL-Statement von Jürgen angepasst, dann gings:

SQL-Code:

SELECT TAGS.ID, TAGS.TERM_NAME AS TN, SUM(EXP_VALUE) AS EXP_VAL 
FROM TAGS 
RIGHT JOIN TAGS_RELATIONSHIPS REL 
ON REL.TERM_ID = TAGS.ID 
JOIN TURNOVER EXP 
ON EXP.ID = REL.ENTRY_ID 
WHERE EXP.EXP_DATE BETWEEN :from_date AND :to_date
GROUP BY TAGS.ID, TAGS.TERM_NAME

[Elvis]

Offtopic, abe tat gerade weh beim hinschauen:
Mache aus den Char-Felder bitte dringenst VarChar.
Char füllt hinten alles mit Spaces auf, VarChar lässt den Inhalt wie er ist. 255 klingt mehr nach einer willkürlichen Größenbeschränkung als nach einer sinnvollen für Tags.
Außerdem solltest du darauf achten deine DB entweder mit Unicode als Default einzurichten, oder zumindest alles was irgendwie erfasst oder dargestellt wird.
Indizes für alle Felder nach denen du filterst und gruppierst wirst du hoffentlich schon haben.
Sonst wird die Abfrage mit zunehmender Datenmenge langsamer.

[Mithrandir]

Moin Elvis,

Zitat:

Mache aus den Char-Felder bitte dringenst VarChar.

Wird gemacht.

Zitat:

Außerdem solltest du darauf achten deine DB entweder mit Unicode als Default einzurichten, oder zumindest alles was irgendwie erfasst oder dargestellt wird.

Zumindest meine Delphiversion bietet keine Unicode-Unterstützung. Imho macht eine Unicode-DB dann wenig Sinn, oder doch? :gruebel:

Zitat:

Indizes für alle Felder nach denen du filterst und gruppierst wirst du hoffentlich schon haben.

*Bahnhof*

Wie mache ich sowas? Das einzige, was ich habe, ist ein AutoInc auf die "ID"-Felder.