Self Join / Nested Join ? Ich hab ne Blockade...

iangillan · 03.01.2008, 20:56

Hallo Fachleute,

obwohl ich kein SQL-Anfänger bin, verzweifle ich z.Z. an folgender Situation:

Kontext:
Pflegeeinrichtungen haben sich auf verschiedene Pflegeschwerpunkte spezialisiert.
Die Pflegeschwerpunkte sind zahlenmäßig dynamisch. Sprich: es können kurz/mittel/langfristig
weitere Pflegeschwerpunkte hinzukommen und sollen sofort allen Pflegeeinrichtungen zur Auswahl zur Verfügung stehen.
Genauso können Pflegeschwerpunkte im Rahmen einer generellen Neustrukturierung wegfallen. Stritig ist noch,
ob dann die Daten gelöscht, oder deaktiviert werden.

ERM

PE N:M SPEZIALISIERUNG

daher
PE 1:N SPEZIALISIERUNG_ZUORDNUNG N:1 SPEZIALISIERUNG
----------------------------------------------

3 Tabellen

PE
(pe_id, pe_name... weitere Spalten)

SPEZIALISIERUNG
(pe_spezial_ID, spezialisierung)

PE_SPEZIALISIERUNG_ZUORDNUNG
(pe_spezial_ID, pe_id)
----------------------------------------------

DATEN:

PE
pe_id
pe0129
pe0130
pe0131

SPEZIALISIERUNG_ZUORDNUNG
pe_spezial_id pe_id
sp0003 pe0129
sp0001 pe0131
sp0002 pe0129
sp0002 pe0130
sp0001 pe0130
sp0001 pe0129
sp0005 pe0129
sp0007 pe0129

SPEZIALISIERUNG
pe_spezial_ID
sp0001
sp0002
sp0003
sp0005
sp0007
----------------------------------------------

ANFRAGE:
"Gib mir die Menge aller pe_id, für die die Einträge sp0001 UND sp0002 UND... vorhanden sind!"

Diese Anfrage können Nutzer dann m.H. eines PHP-Formulars mittels Checkboxen erstellen,
um die Suchmenge aller Pflegeeinrichtungen auf Grund der Pflegeschwerpunkte einzuschränken.

------------------------------------------------

So, ich bin völlig blockiert. Wahrscheinlich ist es simpel, aber ich komme einfach nicht auf die
adäquate SQL-Anweisung. Ich nutze MYSQL 4.0.25 (UNIX)

Ich bin über Anregungen sehr sehr dankbar!

Ian

Zergling-new · 03.01.2008, 21:46

Hallo und herzlich willkommen hier bei uns

Ich hoffe ich hab dich richtig verstanden:

Code:

SELECT *
FROM `SPEZIALISIERUNG_ZUORDNUNG`
WHERE `pe_spezial_id` IN ('sp0001', 'sp0002', ..)

Damit holst du dir alle Verknüpfungs-Datensätze für die entsprechenden SP.
Dann musst du nurnoch aus der Haupttabelle die passenden PE joinen:

Code:

INNER JOIN `PE`
ON `PE`.`pe_id` = `SPEZIALISIERUNG_ZUORDNUNG`.`pe_id`

Und jetzt noch gruppieren, weil doppelte PEs brauchen wir nicht:

Code:

GROUP BY `PE`.`pe_id`

Macht zusammen:

Code:

SELECT `p`.*
FROM `SPEZIALISIERUNG_ZUORDNUNG` AS `x`
INNER JOIN `PE` AS `p`
ON `p`.`pe_id` = `x`.`pe_id`
WHERE `x`.`pe_spezial_id` IN ('sp0001', 'sp0002', ..)
GROUP BY `p`.`pe_id`

Sollte klappen.

iangillan · 03.01.2008, 22:11

Hi Zergling,

vielen Dank für den superschnellen reply!

Ich habe mich etwas umständlich ausgedrückt.
Es sollen nur die pe_id 's ausgegeben werden, die SOWOHL sp0001, als auch sp0002, als auch ... als Eintrag haben.
Laut meiner Daten wären das pe0129 und pe0130

Fragt man dann auch noch nach sp0003, dann darf nur noch pe0129 im Resultset verbleiben.

That's my problem...

Ian

PS:
Hier mal zum Testen die DDL und DML

CREATE TABLE `pe_spezialisierung_zuordnung` (
`pe_spezial_id` varchar(6) NOT NULL,
`pe_id` varchar(6) NOT NULL,
PRIMARY KEY(`pe_spezial_id`,`pe_id`)
);

CREATE TABLE `pespezialisierung` (
`pe_spezial_id` varchar(6) NOT NULL,
`spezialisierung` varchar(150) NOT NULL,
PRIMARY KEY(`pe_spezial_id`)
);

CREATE TABLE `pe` (
`pe_id` varchar(6) NOT NULL,
`pe_name` varchar(100) NOT NULL,
PRIMARY KEY(`pe_id`)
);

INSERT INTO `pe` (`pe_id`, `pe_name`) VALUES('pe0129', 'test30');
INSERT INTO `pe` (`pe_id`, `pe_name`) VALUES('pe0130', 'test31');
INSERT INTO `pe` (`pe_id`, `pe_name`) VALUES('pe0131', 'test32');

INSERT INTO `pespezialisierung` (`pe_spezial_id`, `spezialisierung`) VALUES('sp0001', 'Wachkoma / Remissionsphasen');
INSERT INTO `pespezialisierung` (`pe_spezial_id`, `spezialisierung`) VALUES('sp0002', 'medizinisch- therapeutisch nicht beeinflussbaren Demenz');
INSERT INTO `pespezialisierung` (`pe_spezial_id`, `spezialisierung`) VALUES('sp0003', 'langzeitbeatmete Pflegebedürftige');
INSERT INTO `pespezialisierung` (`pe_spezial_id`, `spezialisierung`) VALUES('sp0004', 'Suchterkrankungen und psychiatrischen Erkrankungen alter Menschen');
INSERT INTO `pespezialisierung` (`pe_spezial_id`, `spezialisierung`) VALUES('sp0005', 'geistig und mehrfache Behinderung');
INSERT INTO `pespezialisierung` (`pe_spezial_id`, `spezialisierung`) VALUES('sp0007', 'Blinde');

INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0004', 'pe0129');
INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0003', 'pe0129');
INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0001', 'pe0131');
INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0002', 'pe0129');
INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0002', 'pe0130');
INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0007', 'pe0065');
INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0001', 'pe0130');
INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0001', 'pe0129');
INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0005', 'pe0129');
INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0007', 'pe0129');

Zergling-new · 04.01.2008, 01:19

Versuch mal nach `pe_id` zu gruppieren und dann gleich die Anzahl der Übereinstimmungen zu zählen:

Code:

SELECT *
FROM `pe_spezialisierung_zuordnung` AS `x`
INNER JOIN `pe` AS `p`
ON `p`.`pe_id` = `x`.`pe_id` 
WHERE `x`.`pe_spezial_id` IN ('sp0001', 'sp0002')
GROUP BY `x`.`pe_id`
HAVING COUNT(`x `.`pe_spezial_id`) = 2

Wobei 2 am Ende natürlich von der Anzahl der pe_spezial_id's abhängt. In der Zuordnungstabelle dürfen auch keine Duplikate drin vorkommen, aber das ist ja eigentlich auch selbstverständlich.

Klappt das? Kanns grad nicht testen.

iangillan · 04.01.2008, 09:25

Zitat:

Zitat von Zergling

Klappt das? Kanns grad nicht testen.

NEIN
MySQL meldet:
#1109 - Unknown table 'x ' in having clause

Zergling-new · 04.01.2008, 12:09

Entfern mal das Leerzeichen hinter dem x in COUNT()

dr.e. · 04.01.2008, 12:13

<OT>
Lang lebe C&P Engeneering Ltd.

</OT>

iangillan · 05.01.2008, 00:20

Hi Zergling,

es scheint zu funktionieren.
Habe es ins PHP-Script programmiert, in dem dann dynamisch bzw. parameterisiert eine komplextere SQL-Anweisung zusammengestellt wird.
In diesem Rahmen operiert dann der (dein) Teilbereich.

Was soll ich sagen, außer tausend Dank?
Oder der ist auch gut: "phpfriend.de was a true friend"!!! (-:
Wenn ihr einen Spenden-Link auf eurer Seite hättet, wäre da jetzt was rübergewachst. Falls in Planung, sag Bescheid, ich schicke 'ne PayPal-Summe.

Ansonsten kann ich dir nur einen (Aus)Lacher anbieten:
Ich bin zertifizierter ORACLE-DBA (9i) und habe 1 Jahr bei der Firma SYBASE gearbeitet. Ist schon einige Jahre her, aber immerhin...
Ich meiner DB-Hochzeit haben wir seitenlange SQL-Statements, stored procedures und trigger geschrieben.
Und nun bekomme ich die einfachsten SQL-Sachen nicht mehr hin...
Da hilft es auch nicht, dass ich schon über 40 bin und nicht mehr alle Haare auf dem Kopf habe, oder... ((-:

Oder ich komme mit 'ner Ausrede: Ja, als Sub-Select-Statment hatte ich die Anweisung lange im Kopf, aber MYSQL 4.0 untersützt so was nicht!

However, ich werde eure Seite speichern und weiter empfehlen.
Wenn ich die Applikation endgültig fertig habe, kann ich ja noch einmal eine abschließende Wasserstandsmeldung geben.

Bis dahin

Cheerio

Ian

PS:
@dr.e.
Was/Wer ist C&P Engeneering Ltd.?

Zergling-new · 05.01.2008, 13:05

Kein Problem!

Zitat:

Wenn ich die Applikation endgültig fertig habe, kann ich ja noch einmal eine abschließende Wasserstandsmeldung geben.

Wenn du die Zeit findest gerne!

dr.e. · 05.01.2008, 17:59

Zitat:

PS:
@dr.e.
Was/Wer ist C&P Engeneering Ltd.?

Naja, Code copieren, nicht validieren und sich hinterher beschwerden, dass etwas nicht geht ohne sein Hirn selbst anzustrengen...

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
[gelöst]LEFT JOIN, DINSTINCT und trotzdem doppelte?!?!	stefanjann	Datenbanken	10	06.02.2008 11:49
JOIN / LEFT JOIN und Co	prinzli	Datenbanken	12	05.11.2007 22:15
Problem mit Inner join	Jabi	Datenbanken	15	09.05.2006 14:10
inner join aus db		PHP Tipps 2006	8	22.01.2006 01:23

03.01.2008, 20:56
iangillan Neuer Benutzer Registriert seit: 03.01.2008 Beiträge: 5	Self Join / Nested Join ? Ich hab ne Blockade... Hallo Fachleute, obwohl ich kein SQL-Anfänger bin, verzweifle ich z.Z. an folgender Situation: Kontext: Pflegeeinrichtungen haben sich auf verschiedene Pflegeschwerpunkte spezialisiert. Die Pflegeschwerpunkte sind zahlenmäßig dynamisch. Sprich: es können kurz/mittel/langfristig weitere Pflegeschwerpunkte hinzukommen und sollen sofort allen Pflegeeinrichtungen zur Auswahl zur Verfügung stehen. Genauso können Pflegeschwerpunkte im Rahmen einer generellen Neustrukturierung wegfallen. Stritig ist noch, ob dann die Daten gelöscht, oder deaktiviert werden. ERM PE N:M SPEZIALISIERUNG daher PE 1:N SPEZIALISIERUNG_ZUORDNUNG N:1 SPEZIALISIERUNG ---------------------------------------------- 3 Tabellen PE (pe_id, pe_name... weitere Spalten) SPEZIALISIERUNG (pe_spezial_ID, spezialisierung) PE_SPEZIALISIERUNG_ZUORDNUNG (pe_spezial_ID, pe_id) ---------------------------------------------- DATEN: PE pe_id pe0129 pe0130 pe0131 SPEZIALISIERUNG_ZUORDNUNG pe_spezial_id pe_id sp0003 pe0129 sp0001 pe0131 sp0002 pe0129 sp0002 pe0130 sp0001 pe0130 sp0001 pe0129 sp0005 pe0129 sp0007 pe0129 SPEZIALISIERUNG pe_spezial_ID sp0001 sp0002 sp0003 sp0005 sp0007 ---------------------------------------------- ANFRAGE: "Gib mir die Menge aller pe_id, für die die Einträge sp0001 UND sp0002 UND... vorhanden sind!" Diese Anfrage können Nutzer dann m.H. eines PHP-Formulars mittels Checkboxen erstellen, um die Suchmenge aller Pflegeeinrichtungen auf Grund der Pflegeschwerpunkte einzuschränken. ------------------------------------------------ So, ich bin völlig blockiert. Wahrscheinlich ist es simpel, aber ich komme einfach nicht auf die adäquate SQL-Anweisung. Ich nutze MYSQL 4.0.25 (UNIX) Ich bin über Anregungen sehr sehr dankbar! Ian


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

03.01.2008, 21:46
Zergling-new Erfahrener Benutzer Registriert seit: 21.05.2008 Beiträge: 9.937	Hallo und herzlich willkommen hier bei uns Ich hoffe ich hab dich richtig verstanden: Code: SELECT * FROM `SPEZIALISIERUNG_ZUORDNUNG` WHERE `pe_spezial_id` IN ('sp0001', 'sp0002', ..) Damit holst du dir alle Verknüpfungs-Datensätze für die entsprechenden SP. Dann musst du nurnoch aus der Haupttabelle die passenden PE joinen: Code: INNER JOIN `PE` ON `PE`.`pe_id` = `SPEZIALISIERUNG_ZUORDNUNG`.`pe_id` Und jetzt noch gruppieren, weil doppelte PEs brauchen wir nicht: Code: GROUP BY `PE`.`pe_id` Macht zusammen: Code: SELECT `p`.* FROM `SPEZIALISIERUNG_ZUORDNUNG` AS `x` INNER JOIN `PE` AS `p` ON `p`.`pe_id` = `x`.`pe_id` WHERE `x`.`pe_spezial_id` IN ('sp0001', 'sp0002', ..) GROUP BY `p`.`pe_id` Sollte klappen.

03.01.2008, 22:11
iangillan Neuer Benutzer Registriert seit: 03.01.2008 Beiträge: 5	Hi Zergling, vielen Dank für den superschnellen reply! Ich habe mich etwas umständlich ausgedrückt. Es sollen nur die pe_id 's ausgegeben werden, die SOWOHL sp0001, als auch sp0002, als auch ... als Eintrag haben. Laut meiner Daten wären das pe0129 und pe0130 Fragt man dann auch noch nach sp0003, dann darf nur noch pe0129 im Resultset verbleiben. That's my problem... Ian PS: Hier mal zum Testen die DDL und DML CREATE TABLE `pe_spezialisierung_zuordnung` ( `pe_spezial_id` varchar(6) NOT NULL, `pe_id` varchar(6) NOT NULL, PRIMARY KEY(`pe_spezial_id`,`pe_id`) ); CREATE TABLE `pespezialisierung` ( `pe_spezial_id` varchar(6) NOT NULL, `spezialisierung` varchar(150) NOT NULL, PRIMARY KEY(`pe_spezial_id`) ); CREATE TABLE `pe` ( `pe_id` varchar(6) NOT NULL, `pe_name` varchar(100) NOT NULL, PRIMARY KEY(`pe_id`) ); INSERT INTO `pe` (`pe_id`, `pe_name`) VALUES('pe0129', 'test30'); INSERT INTO `pe` (`pe_id`, `pe_name`) VALUES('pe0130', 'test31'); INSERT INTO `pe` (`pe_id`, `pe_name`) VALUES('pe0131', 'test32'); INSERT INTO `pespezialisierung` (`pe_spezial_id`, `spezialisierung`) VALUES('sp0001', 'Wachkoma / Remissionsphasen'); INSERT INTO `pespezialisierung` (`pe_spezial_id`, `spezialisierung`) VALUES('sp0002', 'medizinisch- therapeutisch nicht beeinflussbaren Demenz'); INSERT INTO `pespezialisierung` (`pe_spezial_id`, `spezialisierung`) VALUES('sp0003', 'langzeitbeatmete Pflegebedürftige'); INSERT INTO `pespezialisierung` (`pe_spezial_id`, `spezialisierung`) VALUES('sp0004', 'Suchterkrankungen und psychiatrischen Erkrankungen alter Menschen'); INSERT INTO `pespezialisierung` (`pe_spezial_id`, `spezialisierung`) VALUES('sp0005', 'geistig und mehrfache Behinderung'); INSERT INTO `pespezialisierung` (`pe_spezial_id`, `spezialisierung`) VALUES('sp0007', 'Blinde'); INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0004', 'pe0129'); INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0003', 'pe0129'); INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0001', 'pe0131'); INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0002', 'pe0129'); INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0002', 'pe0130'); INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0007', 'pe0065'); INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0001', 'pe0130'); INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0001', 'pe0129'); INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0005', 'pe0129'); INSERT INTO `pe_spezialisierung_zuordnung` (`pe_spezial_id`, `pe_id`) VALUES('sp0007', 'pe0129');

04.01.2008, 01:19
Zergling-new Erfahrener Benutzer Registriert seit: 21.05.2008 Beiträge: 9.937	Versuch mal nach `pe_id` zu gruppieren und dann gleich die Anzahl der Übereinstimmungen zu zählen: Code: SELECT * FROM `pe_spezialisierung_zuordnung` AS `x` INNER JOIN `pe` AS `p` ON `p`.`pe_id` = `x`.`pe_id` WHERE `x`.`pe_spezial_id` IN ('sp0001', 'sp0002') GROUP BY `x`.`pe_id` HAVING COUNT(`x `.`pe_spezial_id`) = 2 Wobei 2 am Ende natürlich von der Anzahl der pe_spezial_id's abhängt. In der Zuordnungstabelle dürfen auch keine Duplikate drin vorkommen, aber das ist ja eigentlich auch selbstverständlich. Klappt das? Kanns grad nicht testen.

04.01.2008, 12:09
Zergling-new Erfahrener Benutzer Registriert seit: 21.05.2008 Beiträge: 9.937	Entfern mal das Leerzeichen hinter dem x in COUNT()

04.01.2008, 12:13
dr.e. Moderator und Wett-König Registriert seit: 21.05.2008 Beiträge: 3.654 PHP-Kenntnisse: Fortgeschritten	<OT> Lang lebe C&P Engeneering Ltd. </OT> __________________ Viele Grüße, Dr.E. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. Think about software design before you start to write code! 2. Discuss and review it together with experts! 3. Choose good tools (-> Adventure PHP Framework (APF))! 4. Write clean and reusable software only! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

05.01.2008, 00:20
iangillan Neuer Benutzer Registriert seit: 03.01.2008 Beiträge: 5	Hi Zergling, es scheint zu funktionieren. Habe es ins PHP-Script programmiert, in dem dann dynamisch bzw. parameterisiert eine komplextere SQL-Anweisung zusammengestellt wird. In diesem Rahmen operiert dann der (dein) Teilbereich. Was soll ich sagen, außer tausend Dank? Oder der ist auch gut: "phpfriend.de was a true friend"!!! (-: Wenn ihr einen Spenden-Link auf eurer Seite hättet, wäre da jetzt was rübergewachst. Falls in Planung, sag Bescheid, ich schicke 'ne PayPal-Summe. Ansonsten kann ich dir nur einen (Aus)Lacher anbieten: Ich bin zertifizierter ORACLE-DBA (9i) und habe 1 Jahr bei der Firma SYBASE gearbeitet. Ist schon einige Jahre her, aber immerhin... Ich meiner DB-Hochzeit haben wir seitenlange SQL-Statements, stored procedures und trigger geschrieben. Und nun bekomme ich die einfachsten SQL-Sachen nicht mehr hin... Da hilft es auch nicht, dass ich schon über 40 bin und nicht mehr alle Haare auf dem Kopf habe, oder... ((-: Oder ich komme mit 'ner Ausrede: Ja, als Sub-Select-Statment hatte ich die Anweisung lange im Kopf, aber MYSQL 4.0 untersützt so was nicht! However, ich werde eure Seite speichern und weiter empfehlen. Wenn ich die Applikation endgültig fertig habe, kann ich ja noch einmal eine abschließende Wasserstandsmeldung geben. Bis dahin Cheerio Ian PS: @dr.e. Was/Wer ist C&P Engeneering Ltd.?