Select * from table mit right join + for each Person_ID möglich?

Hallo zusammen,

mein erster post in diesem Forum, vielleicht nur ganz kurz zu mir: PHP und MYSQL sind für mich ein Hobby. Klingt komisch, ist aber so Ich bastle an einer seite zur Vortrags-planung/verwaltung in einem Verein. Alles funktioniert, nur will ich ein paar queries verbessern.

Ich habe eine Tabelle, in der für 4 Personen eingetragen ist, in welchem Monat sie in welchem Verein einen Vortrag gehalten haben. Maximal wird pro Person ein Vortrag im Monat geplant, an manchen Monaten hat eine Person vielleicht aber auch keinen Vortrag. Bisher haeb ich über PHP die query 4 mal angestossen und jeweils pro Person abgefragt. Bekomme ich sowas auch in einer einzelnen Query hin? Das würde auch einiges an code und loops im PHP sparen.

soweit bin ich im moment mit der neuen Query:

SELECT * FROM

( SELECT external_plan.Talkdate, external_plan.person_ID, congregation_has_person.Congregation_ID from `external_plan`
LEFT JOIN congregation_has_person ON congregation_has_person.Person_ID = external_plan.Person_ID
WHERE YEAR(talkDate) = 2013
AND congregation_has_person.Congregation_ID = 1
ORDER BY MONTH(external_plan.Talkdate), external_plan.Person_ID
) ep

RIGHT JOIN monthnumbers ON MONTH(ep.TalkDate) = monthnumbers.months

mit dem RIGHT JOIN will ich erreichen (tabelle monthnumbers enthalt nur die monate 1 - 12 als nummern zum vergleich), dass mir auch die monate angezeigt werden, in denen für eine person kein Vortrag geplant ist. Wo ich festsitze ist jetzt, dass die Query PRO MONAT für alle 4 Personen auf einmal die 12 Monate anzeigt, ich bräuchte aber eine Anzeige PRO MONAT UND PRO PERSON.

Wie gesagt, ist nur ein hobby, also nix dringendes ( DROP TABLE vorschläge sind trotzdem unerwünscht LOL )

Grüße
Helmut

 

Ja, MySQL als Hobby zu betreiben ist wahrlich komisch ;-)

SQL in Schleifen ist faktisch nie eine gute Idee, das hast ja erkannt.

Nun, ich bevorzuge lieber PostgreSQL, aber das tut hier jetzt prinzipiell nix zur Sache. Du brauchst erst einmal eine Zusammenstellung der personen und der Zeiträume. Also eine Aufstellung aller möglichen Kombinationen, ähnlich Deiner Monats-Hilfstabelle.

Ich hab mal folgende Tabellen gemacht:

Code:

test=*# \d vortraege;
  Table "public.vortraege"
  Column  |  Type  | Modifiers
-------------+---------+-----------
vortrags_id | integer | not null
datum  | date  |
titel  | text  |
Indexes:
  "vortraege_pkey" PRIMARY KEY, btree (vortrags_id)
Referenced by:
  TABLE "gehaltene_vortraege" CONSTRAINT "gehaltene_vortraege_v_id_fkey" FOREIGN KEY (v_id) REFERENCES vortraege(vortrags_id)

test=*# \d personen;
  Table "public.personen"
  Column  |  Type  | Modifiers
-----------+---------+-----------
person_id | integer | not null
name  | text  |
Indexes:
  "personen_pkey" PRIMARY KEY, btree (person_id)
Referenced by:
  TABLE "gehaltene_vortraege" CONSTRAINT "gehaltene_vortraege_p_id_fkey" FOREIGN KEY (p_id) REFERENCES personen(person_id)

test=*# \d gehaltene_vortraege;
Table "public.gehaltene_vortraege"
Column |  Type  | Modifiers
--------+---------+-----------
v_id  | integer |
p_id  | integer |
Foreign-key constraints:
  "gehaltene_vortraege_p_id_fkey" FOREIGN KEY (p_id) REFERENCES personen(person_id)
  "gehaltene_vortraege_v_id_fkey" FOREIGN KEY (v_id) REFERENCES vortraege(vortrags_id)

mit den daten:

Code:

test=*# select * from vortraege ;
vortrags_id |  datum  |  titel  
-------------+------------+-----------
  1 | 2013-01-10 | vortrag 1
  2 | 2013-02-20 | vortrag 2
  3 | 2013-03-15 | vortrag 3
(3 rows)  

Time: 0,162 ms
test=*# select * from personen ;
person_id |  name  
-----------+---------  
  1 | person1  
  2 | person2  
  3 | person3  
(3 rows)  

Time: 0,140 ms
test=*# select * from gehaltene_vortraege ;
v_id | p_id
------+------
  1 |  1
  2 |  2
  2 |  3
(3 rows)

Außerdem 2 Views:

Code:

test=*# \d+ view_vortraege
  View "public.view_vortraege"
  Column  |  Type  | Modifiers | Storage  | Description
-------------+------------------+-----------+----------+-------------
vortrags_id | integer  |  | plain  |
datum  | date  |  | plain  |
titel  | text  |  | extended |
monat  | double precision |  | plain  |
View definition:
SELECT vortraege.vortrags_id,
  vortraege.datum,
  vortraege.titel,
  date_part('month'::text, vortraege.datum) AS monat
  FROM vortraege;

test=*# \d+ view_person_monat
  View "public.view_person_monat"
  Column  |  Type  | Modifiers | Storage | Description
-----------+---------+-----------+---------+-------------
person_id | integer |  | plain  |
s  | integer |  | plain  |
View definition:
SELECT personen.person_id,
  s.s
  FROM personen
  CROSS JOIN generate_series(1, 12) s(s);

Dieser View enthält oben genannte Aufstellung aller Personen und Monate. Der erste View dient lediglich der Vereinfachung (extra Spalte mit der Monatsnummer)

Nun sage ich:

Code:

test=*# select pm.s as monat, pm.person_id, v.datum, p.name from gehaltene_vortraege gv left join personen p on gv.p_id=p.person_id left join view_vortraege v on gv.v_id=v.vortrags_id right join view_person_monat pm on ((pm.person_id, pm.s)=(p.person_id,v.monat));  
monat | person_id |  datum  |  name  
-------+-----------+------------+---------  
  1 |  1 | 2013-01-10 | person1  
  1 |  2 |  |  
  1 |  3 |  |  
  2 |  1 |  |  
  2 |  2 | 2013-02-20 | person2  
  2 |  3 | 2013-02-20 | person3  
  3 |  1 |  |  
  3 |  2 |  |  
  3 |  3 |  |  
  4 |  1 |  |  
  4 |  2 |  |  
  4 |  3 |  |  
  5 |  1 |  |  
  5 |  2 |  |  
  5 |  3 |  |  
  6 |  1 |  |  
  6 |  2 |  |  
  6 |  3 |  |  
  7 |  1 |  |  
  7 |  2 |  |  
  7 |  3 |  |  
  8 |  1 |  |  
  8 |  2 |  |  
  8 |  3 |  |  
  9 |  1 |  |  
  9 |  2 |  |  
  9 |  3 |  |  
  10 |  1 |  |  
  10 |  2 |  |  
  10 |  3 |  |  
  11 |  1 |  |  
  11 |  2 |  |  
  11 |  3 |  |  
  12 |  1 |  |  
  12 |  2 |  |  
  12 |  3 |  |  
(36 rows)

Durch den View view_person_monat erhalte ich durch den CROSS JOIN alle Leute für je jeden Monat. Damit arbeite ich dann weiter.

Kann man sicher verbessern, das funktioniert ja schon nicht mehr richtig wenn da Termine aus 2 oder mehr Jahren drin sind.



Übrigens: Du schriebst ja maximal ein Vortrag / Monat und Person. Wie stellst Du das sicher? Im PostgreSQL könntest Du, falls das eine 'harte' Bedingung ist, diese Forderung innerhalb der Datenbank abbilden, mit einem Exclusion Constraint. Hab im hier im PG-Unterforum mal demonstriert, wie das geht.

 

na ja...mein Beruf: Netzwerktechniker: andere sammeln zum Entspannen Briefmarken, ich hab spass an PHP (als nächstes bastle ich mir eine Website zur Steuerung der Gartenbewässerung )

Die Views sind neu für mich gewesen. Ich hab mich etwas eingelesen und bin begeistert. Bin mir sicher, dass ich hier eine passende Lösung gefunden habe.

@ akretschmer : Vielen Dank für die Mühe, die Antwort zusammen zu stellen. Die ist mehr als ausführlich und verständlich. Ich weis das zu Schätzen! Du hast miene Frage absolut beantwortet!

 

Du hast meine obligatorischen MySQL-Rant nicht verstanden. Aber ich bin mir sicher, das klappt schon noch. Irgendwann.