Select über 3 Tabellen

[coastbike]

Wer kann helfen?

Ich habe 3 Tabellen. In Tabelle A sind Adressen mit der Spalte 'namen'. In Tabelle B sind Gegenstände mit einer Nummer und einer Adresse aus A. In Tabelle C sind Nutzungszeiten der Gegenstände mit von, bis und einer Nummer aus B.

Tabelle A: 'name'
Tabelle B: 'nummer', 'name'(aus A)
Tabelle C: 'von', 'bis', 'nummer'(aus B)

Eigentlich ganz einfach. Aber nun:

Ich will nicht die Zeiten filten zwischen 'von' und 'bis' sondern die Zeit, ausgehend von einem bestimmten Datum (z.Bsp. heute) vom letzten Rücklauf (max(bis) <= heute, aus C) und der nächsten Vergabe (min(von >= heute, aus C). Dazu möchte ich über Tabelle B den 'name' aus Tabelle A und die Tage zw. Rücklauf und neuer Vergabe.

Was ich nicht haben möchte ist
* einen max(bis) von 'xyz' z.Bsp. vorgestern
* einen min(von) ebenfalls von 'xyz' z.Bsp. übermorgen
* aber von gestern bis morgen ist 'xyz' vergeben

Woran ich immer wieder scheitere, sind die drei *

SELECT `tab_a1`.`vname`,`tab_a1`.`name`,`tab_c1`.`nr`, max(`tab_c1`.`bis`) `bis`, min(`tab_c2`.`von`) `von` 
FROM `tab_c` `tab_c1` 
LEFT JOIN  `tab_c` `tab_c2` 
ON `tab_c2`.`nr` = `tab_c2`.`nr`
LEFT JOIN  `tab_b` `tab_b1` 
ON `tab_b1`.`nr` = `tab_c2`.`nr`
LEFT JOIN  `tab_a` `tab_a1` 
ON `tab_a1`.`name` = `tab_b1`.`name`
WHERE  `tab_c1`.`bis` <= '2009-06-03' and `tab_c2`.`von` >= '2009-06-03' 
GROUP BY `tab_c1`.`nr` 
ORDER BY `tab_c1`.`nr` LIMIT 100 

Gruß coastbike

[coastbike]

nachdem ich beim warten auf Antwort weiter geknobelt habe, bin ich zu folgenden Stand gekommen

SELECT `tab_a`.`name`, `tab_c1`.`nr`, 
MAX(`tab_c2`.`bis`) `bis2`, 
MIN(`tab_c1`.`von`) `von1`,
DATEDIFF(MIN(`tab_c1`.`von`),MAX(`tab_c2`.`bis`)) `Diff`
FROM `tab_c` `tab_c1`,`tab_c` `tab_c2`, `tab_b`, `tab_a`
WHERE `tab_c1`.`obj_nr` =`tab_c2`.`obj_nr`
AND `tab_c1`.`beleg` <> `tab_c2`.`beleg`
AND `tab_c1`.`nr` = `tab_b`.`nr`
AND `tab_c1`.`von` >= '2009-06-03' 
AND `tab_c2`.`bis` <= '2009-06-03'
AND `tab_a`.`id_eg` = `tab_b`.`id_eg`
GROUP BY `tab_c1`.`obj_nr` 
ORDER BY `tab_c1`.`obj_nr` 

Nur leider schmeist mich folgende Zeile raus

DATEDIFF(MIN(`tab_c1`.`von`),MAX(`tab_c2`.`bis`)) `Diff` 

Es kommt folgende Fehlermeldung

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( MIN( `beleg1` . `von` ) , MAX( `beleg2` . `bis` ) ) `Diff` F

MIN(`tab_c1`.`von`) ist das gößere von beiden Daten. Also das ist es auch nicht.

Gruß coastbike

[Falk Prüfer]

Hallo coastbike,

sorry, aber ich verstehe weder was du willst,

...Ich will nicht ... sondern die Zeit, ausgehend von einem bestimmten Datum ... vom letzten Rücklauf ... und der nächsten Vergabe .... Dazu möchte ich über Tabelle B den 'name' aus Tabelle A und die Tage zw. Rücklauf und neuer Vergabe.

(Irgendwie ergibt das für mich keinen Sinn. Welche Zeit willst du?)

Noch dein Statement.

SELECT `tab_a`.`name`, `tab_c1`.`nr`,
MAX(`tab_c2`.`bis`) `bis2`,
MIN(`tab_c1`.`von`) `von1`,
DATEDIFF(MIN(`tab_c1`.`von`),MAX(`tab_c2`.`bis`)) `Diff`
FROM `tab_c` `tab_c1`,`tab_c` `tab_c2`, `tab_b`, `tab_a`
WHERE `tab_c1`.`obj_nr` =`tab_c2`.`obj_nr`
AND `tab_c1`.`beleg` <> `tab_c2`.`beleg`
AND `tab_c1`.`nr` = `tab_b`.`nr`
AND `tab_c1`.`von` >= '2009-06-03'
AND `tab_c2`.`bis` <= '2009-06-03'
AND `tab_a`.`id_eg` = `tab_b`.`id_eg`
GROUP BY `tab_c1`.`obj_nr`
ORDER BY `tab_c1`.`obj_nr`
[/PHP]

`tab_c1`.`obj_nr` steht nicht in der Select-Liste, warum dann im GROUP BY? Dafür fehlen im GROUP BY die nichtaggregierten Felder (`tab_a`.`name`, `tab_c1`.`nr`) aus der Select-Liste!
Die Fehlermeldung "...near '( MIN( `beleg1` . `von` ) ..." passt auch nicht zum geposteten Statement "...(MIN(`tab_c1`.`von`)...". Sind die Leerzeichen im (offensichtlich) originalen Statement tatsächlich so drin?

Gruß Falk

[coastbike]

Danke für die Antwort, Falk!

Bsp.: Ich hab ein Buch, Rasenmäher, Bohrmaschine, Flex usw., die ich von Zeit zu Zeit weg gebe. Nun ist das Merken so eine Sache. Und es liegt auch nicht alles immer vor mir. Also muss eine Verwaltung her. Dazu habe ich

tab_a (Nutzertabelle) mit der Spalte `namen`
tab_b (Gegenstandstabelle) mit den Spalten `nummer`und `name`, wobei `name` identisch ist mit tab_a
tab_c (Zeittabelle) mit den Spalten `von`, `bis` und `nummer`, wobei `nummer` identisch ist mit tab_b

Es gehören noch mehr Spalten dazu, aber die sind im Moment nicht wichtig.

Jetzt will ich eine Liste mit allen Gegenständen, die bei mir sind und wie lange sie noch bei mir sind, bis sie wieder raus gehen. Dazu muss berücksichtigt werden, das Neuanschaffungen noch gar nicht weg waren und Gegenstände noch nicht wieder erfragt bzw. reserviert wurden. Auch möchte ich wissen in wie viel Tagen ein Gegenstand zum Nachbarn geht (meine Flex z. Bsp.). Übrigens danke für Deinen Fehlerhinweis. Dazu habe ich erstmal das:

        SELECT `tab_a`.`name`, 
                `tab_c1`.`nummer`, 
                MAX(`tab_c2`.`bis`) `bis2`, 
                MIN(`tab_c1`.`von`) `von1`,
                DATEDIFF(MIN(`tab_c1`.`von`),MAX(`tab_c2`.`bis`)) `Diff`
        FROM     `tab_c` `tab_c1`,
                `tab_c` `tab_c2`, 
                `tab_b`, 
                `tab_a`
        WHERE `tab_c1`.`nummer` =`tab_c2`.`nummer`
            AND `tab_c1`.`beleg` <> `tab_c2`.`beleg`
            AND `tab_c1`.`nummer` = `tab_b`.`nummer`
            AND `tab_c1`.`von` >= '2009-06-03' 
            AND `tab_c2`.`bis` <= '2009-06-03'
            AND `tab_a`.`id_eg` = `tab_b`.`id_eg`
        GROUP BY `tab_c1`.`nummer` 
        ORDER BY `tab_c1`.`nummer` 

Unabhängig vom Ergebnis funktioniert DATEDIFF nicht. Aber es ist auch nicht die Lösung. Siehe Einleitung.

Ist Dir dies verständlicher?

Gruss coastbike

[Falk Prüfer]

Hallo,

...Ist Dir dies verständlicher?

Das ist verständlicher und auch gar nicht so schwer
Ich gehe davon aus, das:

• verliehene und nicht zurückgegeben Gegenstände haben kein bis-Datum
• nicht mehr verliehene Gegenstände haben ein bis-Datum das größer als das von-Datum ist
• Reservierte Gegenstände haben ein von-Datum in der Zukunft und ein leeres bis-Datum

Beispieldaten:
[highlight=sql]
create table tab_a (namen varchar(32));
create table tab_b (nummer int(4), name varchar(32));
create table tab_c (von datetime, bis datetime, nummer int(4));
insert into tab_a values
('Bohrmaschine'),
('Flex'),
('Buch'),
('Rasenmäher');
insert into tab_b values
(1, 'Bohrmaschine'),
(2, 'Flex'),
(3, 'Buch'),
(4, 'Rasenmäher');
insert into tab_c values
('2009-03-01 10:32:00', '2009-03-13 12:20:00', 1),
('2009-04-01 10:32:00', '2009-04-13 12:20:00', 1),
('2009-06-01 10:32:00', null, 1),
('2009-03-02 10:32:00', '2009-03-12 12:20:00', 2),
('2009-06-08 10:32:00', null, 2),
('2009-03-03 10:32:00', '2009-03-11 12:20:00', 4);
[/highlight]
Für Datum Heute (05.06.2009) gilt:
Die Bohrmaschine wurde schon mehrfach und ist aktuell verliehen. Die Flex wurde schon einmal verliehen und ist reserviert. Das Buch ist neu und wurde noch nie verliehen. Der Rasenmäher war schonmal verliehen ist es aber aktuell nicht.

Abfrage:
[highlight=sql]
select tab_b.nummer, tab_a.namen, datediff(max(tab_c.von), now()) diff,
if(max(tab_c.von) > max(tab_c.bis),
if(datediff(max(tab_c.von), now()) > 0,
concat('reserviert in: ', datediff(max(tab_c.von), now()), ' Tagen'),
concat('ausgeliehen seit: ', datediff(now(), max(tab_c.von)), ' Tagen')
),
if(max(tab_c.von) < max(tab_c.bis),
concat('liegt bei mir rum seit: ', datediff(now(), max(tab_c.von)), ' Tagen'),
'hab ich noch nie verliehen')
) status
from tab_b
inner join tab_a on tab_a.namen = tab_b.name
left join tab_c on tab_c.nummer = tab_b.nummer
group by tab_b.nummer, tab_a.namen
[/highlight]

Ergebnis:

nummer  | namen         | diff  | status
1       | Bohrmaschine  | -4    | ausgeliehen seit: 4 Tagen
2       | Flex          |  3    | ausleihen in: 3 Tagen
3       | Buch          |  NULL | hab ich noch nie verliehen
4       | Rasenmäher    | -94   | liegt bei mir rum seit 94 Tagen

Gruß Falk

[coastbike]

Hallo Falk,

erst Mal danke für Deine Nachricht. datediff geht bei mir nicht, weil ich bei 1und1 noch mit mysql 4.0 arbeite. Hab aber schon was gefunden. 'to_days'. Bin diese Woche mit meiner Zeit ein bisschen knapp. Werde mich aber auf jeden Fall melden.

gruß coastbike