Joins, group by & order

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

ich hab die folgenden tabellen: „avail“ und „numbers“
numbers ist eine hilfstabelle die ich brauche um die sortierung zu ermöglichen - hat man mir in einem chat gesagt…
avail beinhaltet die verfügbarkeiten des raums: crdate steht dabei für das erstellungsdatum

ich habe mir nun folgenden code zusammen gestöpselt:

SELECT numbers.num, avail.startdate, avail.enddate, avail.numberrooms
FROM (
SELECT num
FROM numbers
WHERE num
BETWEEN (
SELECT MIN( avail.startdate )
FROM avail
)
AND (
SELECT MAX( avail.enddate )
FROM avail
)
) AS numbers

JOIN avail ON num
BETWEEN avail.startdate
AND avail.enddate

LEFT JOIN avail t1 ON num
BETWEEN t1.startdate
AND t1.enddate
AND avail.startdate

#2

Moion, david,

niemand liest gern Code - nicht den eigenen, den von Fremden schon gar nicht - und bastelt sich daraus eine Aufgabenstellung. Schildere Dein Problem aus fachlicher Sicht (Was will der Auftraggeber), dann kann Dir vielleicht jemand helfen.

Gruß Ralf

#3

ok danke für den hinweis.

folgende aufgabenstellung:
ich habe die tabelle „avail“ welche 4 spalten beinhaltet:

  1. crdate (date of creation)
  2. startdate
  3. enddate
  4. numberrooms (number of available rooms)

er soll mir aus dieser nun die tabelle „output“ generieren die mindestens 2 spalten benötigt:

  1. date
  2. numberrooms
  • er soll beim generieren die tabelle avail auswerten,
  • er soll jeden tag im (in einer variable) übergebenen jahr anführen (zb 2008)
  • er soll für jeden tag prüfen wieviele zimmer verfügbar sind
  • falls ein tag 2mal in der tabelle avail vorhanden ist, dann soll er den neueren (crdate) datensatz nehmen
  • falls ein tag in der tabelle avail nicht vorhanden ist, dann soll er den wert aus der tabelle basic nehmen

grafische darstellung der tabellen:
http://www.tfa.w4y.at/___div/dl/mysql_problem3.png

jemand eine idee?
gruß
david

#4

bin gerade draufgekommen dass ich einige infos vergessen habe die zur lösung meines problems beitragen könnten:

  1. all die integer werte sind timestamps (timestamps deswegen weil typo3 dies vorgibt)
  2. die id’s waren in meiner grafischen darstellung nicht unique, bei dem jetzt unten angehängten bild, hab ich diesen fehler ausgebessert
  3. das output datum soll eine tageweise auflistung sein… diese fand man in keiner der beiden alten tabellen… deswegen habe ich nun eine tabelle namens „einzelneTage“ erstellt, welche eben die in „output“ aufgelisteten daten enthält
  4. die checkroutine nochmals genauer ausgeführt:

er soll bei einem gewissen tag (zb 1.1.2008 / als timestamp: 1199142000) anfangen und dann tageweise jeweils checken ob der tag zwischen eines der start- und enddaten in der tabelle „avail“ fällt?
falls nein: er nimmt für den tag den basic output (in diesem fall: 6) her
falls ja: fällt er bei einem oder bei mehreren datensätzen hinein?
…bei einem: er nimmt den numberrooms wert
…bei mehreren: er nimmt den numberroms wert des neueren datensatzes her

hier nochmals die verbesserte version
http://www.tfa.w4y.at/___div/dl/mysql_problem4.png

#5

Ich gebe auf
Hi David,

da ich weiterhin nur Bahnhof verstehe, nochmal der Tipp: Schreib auf, was der Auftraggeber wünscht.

In der Datenstruktur, die Du vorgibst, stecken bereits Überlegungen und Lösungsansätze, die niemand mehr nachvollziehen kann. Und daraus zu schließen, wie aus numberrooms im Zeitraum numberrooms zum Zeitpunkt werden sollen, gelingt mir halt nicht.

Gruß Ralf

#6

Hi!

hier nochmals die verbesserte version
http://www.tfa.w4y.at/___div/dl/mysql_problem4.png

Entweder ich hab’s UND Du hast einen Fehler in Deiner Output-Tabelle … oder ich habs nicht …

Bevor ich irgendeinen Schwachsinn hier empfehle:
Gehört in der Output-Tabelle beim 2.1.2008 (also 1199228400) nicht eine 4 bei numberrooms?

Grüße,
Tomh

#7

argh abschreib fehler… vor lauter datenbank abschreibübungen hab ich mich doch tatsächlich vertan…

wie lautet denn deine lösung?

#8

danke für deine geduld… ich seh den wald vor lauter bäumen schon nicht mehr…
also es geht um zimmerverfügbarkeiten.

ich habe mehrere zimmerarten
– zb doppelzimmer, einzelzimmer,…

jede zimmerart hat eine grundverfügbarkeit
– zb es gibt grundsätzlich 5 doppelzimmer die gebucht werden können

jede zimmerart soll neben dieser grundverfügbarkeit beliebig viele andere verfügbarkeiten haben
– da die zimmer nicht nur per internet gebucht werden können sondern auch direkt vor ort… ist zb auch wichtig wenn man weiß man hat im januar einen kongress wo sowieso 4 doppelzimmer fix gebucht sind. hat den sinn, dass man dann über internet in diesem falle nur mehr 1 doppelzimmer buchen kann

der hotelier kann auserdem sagen ok generell hab ich im märz zb 2 doppelzimmer zur verfügung. jetzt erfährt er aber kurzfristig, dass er in der woche um den 15. märz nur 1 zimmer zur verfügung hat. damit er jetzt nicht seinen bereits getätigten eintrag ändern muss, soll er praktisch einfach den alten eintrag teilweise (nämlich in der woche um den 15. märz) überschreiben können

damit der hotelier nun weiß wieviele zimmer er wann frei hat, bekommt er für jedes zimmer eine übersicht, wo er alle 365 tage des jahres sieht, wo dann bei jedem tag dabei steht wieviele doppelzimmer er zb am 15.märz verfügbar hat.
– das ergebnis in diesem falle wäre dank der letzten änderung wäre: 1 doppelzimmer ist verfügbar

pfuh viel text, aber hoffentlich erklärt es das nun besser als meine vorangegangenen einträge

gruß
david

#9

Hi!

argh abschreib fehler… vor lauter datenbank abschreibübungen
hab ich mich doch tatsächlich vertan…

wie lautet denn deine lösung?

Ich würde das Ganze in Oracle so machen:

select a.date as tag,min(nvl(b.numberrooms,c.numberrooms)) as numberrooms
from tag a
 ,avail b
 ,basic c
where a.tag between b.startdate(+) and b.enddate(+)
 and c.bid in (select d.bid
 from avail d)
group by a.date
order by a.date

und mit dem JOIN-Syntax Zeugs:

select a.date as tag,min(nvl(b.numberrooms,c.numberrooms)) as numberrooms
from tag a 
 RIGHT OUTER JOIN avail b #
 ON a.tag between b.startdate and b.enddate
 JOIN basic c ON c.bid in (select d.bid
 from avail d)
group by a.date

So ungefähr … weder hab ich es getestet noch irgendwie annähernd auf korrekte Syntax überprüft.

Grüße,
Tomh

#10

und mit dem JOIN-Syntax Zeugs:

select a.date as
tag,min(nvl(b.numberrooms,c.numberrooms)) as numberrooms
from tag a
RIGHT OUTER JOIN avail b #
ON a.tag between b.startdate and b.enddate
JOIN basic c ON c.bid in (select d.bid
from avail d)
group by a.date

mh soweit ist mir das konstrukt klar, aber ich bräuchte es für mysql… wie kann man da nvl() umschreiben? oder gibt es da nichts gleichartiges?

tut mir leid, dass ich die mysql info von anfang an vergessen hab

#11

Moin, david,

jede zimmerart soll neben dieser grundverfügbarkeit beliebig
viele andere verfügbarkeiten haben

bis hierher dachte ich, mir wäre klar, was mit Verfügbarkeit gemeint ist. Meine Idee: 5 Zimmer sind vorhanden, 3 gebucht, dann sind noch 2 frei. Was heißt dann „beliebig viele andere“?

damit er jetzt nicht seinen bereits
getätigten eintrag ändern muss

Schreibt er denn bei jeder Reservierung / Buchung auf, wieviel freie Zimmer dann noch übrig sind? Und wo sind diese Reservierungen festgehalten?

wo dann bei jedem tag dabei steht
wieviele doppelzimmer er zb am 15.märz verfügbar hat.

Das geht zwar mit der Tabelle „Tage“, ich halte das aber für einen Kunstfehler. Der Hotelier will doch wissen, ob er für einen nachgefragten Zeitraum ein (oder wie viele auch immer) Zimmer frei hat, was soll er da mit einer Tabelle für die Kalendertage.

Ein Reservierungssystem sollte

  • Reservierungen ermöglichen
  • Reservierungsmöglichkeiten für gewünschte Zeiträume ausweisen

Bist Du sicher, dass Deine Tabellen das hergeben?

Gruß Ralf

#12

mh soweit ist mir das konstrukt klar, aber ich bräuchte es für
mysql… wie kann man da nvl() umschreiben? oder gibt es da
nichts gleichartiges?

ich hab das NVL() jetzt durch ein COALESCE() ersetzt. jetzt funktioniert alles wunderbar…

nur eine frage sei mir noch erlaubt:
bei mir greift er einfach nicht auf die basic tabelle zurück. funktioniert dieser teil bei dir?

#13

es geht eben in diesem teil des systems nicht um reservierungen und buchungen sondern darum, wieviele zimmer der hotelier über’s internet generell buchbar machen will

aber kollege „Tomh“ konnte mir schon ziemlich gut helfen.
danke auch dir für deine hilfe

#14

Hi!

nur eine frage sei mir noch erlaubt:
bei mir greift er einfach nicht auf die basic tabelle zurück.
funktioniert dieser teil bei dir?

Nein, weil ich es weder ausprobiert noch die Tabellen angelegt habe - ev. funktioniert das Sub-Select in MySQL _etwas_ anders bzw. bin ich mit der ganzen JOIN-schreibweise nicht ganz so vertraut, sodaß der JOINS ganz einfach falsch gesetzt ist; ersetzt diesen Join mal durch einen Beistrich und die Verknüpfung gibst Du in eine WHERE-Klausel ungefähr so (so wie beim „Oracle“-Beispiel:

RIGHT OUTER JOIN avail b ON blablabla
,basic c
where c.tag in (select …)

Grüße,
Tomh

PS: Ich sollte ev. doch mal den SQL-Standard erlernen … außer dem FULL OUTER JOIN hab ich das Ganze noch nie benötigt …