Eine Zeile darstellen mit zwei Fremdschlüssel

iCrystal · 27 Juni 2020

Hallo,

mein Problem ist, dass ich ein Primärschlüssel habe und diesen Primärschlüssel muss ich in einer Zeile mit seinen zwei Fremdschlüssel darstellen.

Fallbeispiel:

Ich habe ein Produkt und dieses Produkt gehört zur ein oder zwei Kategorien, falls zwei Kategorien existieren ist eine der beiden Kategorien die Hauptkategorie und die andere die Nebenkategorie.

Dazu habe ich drei Tabellen:

produkt(PID, Bezeichnung, ...)
produktkategorie(PID, KID, HK) --------- HK kennzeichnet hierbei nur, ob die Kategorie eine Hauptkategorie ist oder nicht mit (J / N)
kategorie(KID, Bezeichnung,..)

Ich habe einen Ansatz für die Lösung, jedoch denke ich glaube ich etwas kompliziert, sodass ich nicht darauf komme:

Code:

WITH product_hknk (pid, bez, hauptkategorie, nebenkategorie) AS
(
SELECT p.pid, p.bez, k.bez, k_2.bez
    FROM produkt p
    JOIN produktkategorie pk ON p.pid = pk.pid
    JOIN produktkategorie pk_2 ON p.pid = pk_2.pid
    JOIN kategorie k ON pk.kid = k.kid
    JOIN kategorie k_2 ON pk_2.kid = k_2.kid
     WHERE p.bez LIKE '%tisch%' OR p.bez LIKE '%sessel%'
)
SELECT * FROM product_hknk
GROUP BY pid, bez, hauptkategorie, nebenkategorie
ORDER BY pid;

Mein View schaut dann so aus:

Jedoch, ist mein Problem, dass ich es nicht hinkriege nur eine der Kombination zu zeigen.
Das Endresultat müsste nämlich so ausschauen:

Ich danke Ihnen im Voraus!

akretschmer · 27 Juni 2020

anstatt Bilder wären nachvollzierbare SQL (create, insert) hilfreich.

iCrystal · 27 Juni 2020

akretschmer schrieb:
anstatt Bilder wären nachvollzierbare SQL (create, insert) hilfreich.

Sorry, bloß weiß ich nicht wie ich darauf Zugriff habe, da ich in der Uni an einem bestimmten Schema arbeite, welche uns vom Dozenten vorgegeben wurde.

iCrystal · 27 Juni 2020

Create Kategorie, ProduktKategorie, Produkt:

Code:

CREATE TABLE "U"."KATEGORIE"
   (    "KID" NUMBER(*,0),
    "OBERKATEGORIE" NUMBER(*,0),
    "BEZ" VARCHAR2(100 BYTE),
    "EBENE" NUMBER(*,0)
   )

Code:

CREATE TABLE "U"."PRODUKTKATEGORIE"
   (    "PID" NUMBER(*,0) NOT NULL ENABLE,
    "KID" NUMBER(*,0) NOT NULL ENABLE,
    "HK" CHAR(1 BYTE) NOT NULL ENABLE,
     CHECK ( hk IN (
        'J',
        'N'
    )

Code:

CREATE TABLE "U"."PRODUKT"
   (    "PID" NUMBER(*,0),
    "BEZ" VARCHAR2(100 BYTE),
    "LAENGE_METER" NUMBER(4,1),
    "BREITE_METER" NUMBER(4,1)
   )

INSERT Kategorie und Produkt:

Code:

Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6000',null,'Gartenmoebel','1');
Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6100','6000','Gartentische','2');
Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6110','6100','Esstische','3');
Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6120','6100','Stehtische','3');
Insert into U.KATEGORIE(KID,OBERKATEGORIE,BEZ,EBENE) values ('6200','6000','Gartenbaenke','2');
Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6210','6200','Gartenbaenke - Holz','3');
Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6220','6200','Gartenbaenke - Kunststoff','3');
Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6300','6000','Gartenstuehle','2');
Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6310','6300','Sessel','3');
Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6320','6300','Klappstuehle','3');
Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6400','6000','Pflanzenbehaelter','2');
Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6410','6400','Blumenkaesten','3');
Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6420','6400','Planztische','3');
Insert into U.KATEGORIE (KID,OBERKATEGORIE,BEZ,EBENE) values ('6430','6400','Pflanztoepfe','3');

Code:

Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('61101','Picknicktisch/Bank-Kombination','120',null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('61102','Larum-Esstisch','210','100');
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('61103','Siena-Esstisch','160','90');
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('61104','Lucca-Esstisch',null,null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('61201','Stehtisch rund',null,null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('61202','Stehtisch eckig',null,null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('62102','Gartenbank Sylt','150',null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('62201','Gartenbank Amrum','120',null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('62202','Gartenbank Foehr','120',null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('63101','Lima-Gartensessel','63','71');
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('63102','Mica-Klappsessel','6','70');
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('63201','Diana-Klappstuhl',null,'50');
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('63202','Mira-Klappstuhl',null,'55');
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('63203','Dennis-Klappstuhl',null,'55');
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64101','Elo Standard Blumenkasten','100',null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64102','Elo Profi Blumenkasten','100',null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64103','Flix Blumenkasten','100',null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64104','Flux Blumenkasten','100',null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64105','Flex Blumenkasten','100',null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64201','Draan Standard Planztisch','80','40');
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64202','Merlin Pflanztisch','80','40');
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64301','Pflanztopf Kremo',null,null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64302','Pflanztopf Lanz',null,null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64303','Pflanztopf Kum',null,null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64304','Pflanztopf Kleb',null,null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64305','Pflanztopf Strenz',null,null);
Insert into U.PRODUKT (PID,BEZ,LAENGE_METER,BREITE_METER) values ('64306','Pflanztopf Strunz',null,null);

Sorry für Doppelpost, bloß konnte ich mein Beitrag nicht bearbeiten.

akretschmer · 27 Juni 2020

iCrystal schrieb:
Sorry, bloß weiß ich nicht wie ich darauf Zugriff habe, da ich in der Uni an einem bestimmten Schema arbeite, welche uns vom Dozenten vorgegeben wurde.

Bist recht hilflos, oder? Kann man sich da nicht schnell auf dem privaten Laptop ein schönes PostgreSQL installieren und testen? Müssen Deine Helfer alles machen?

Code:

test=*# select * from produkt;
  pid  |        bez         
-------+--------------------
 61101 | Picknicktisch Bank
 61102 | Larum Fresstisch
 63102 | Klappspaten
(3 rows)

test=*# select * from kategorie ;
 kid |    bez   
-----+-----------
   1 | Tische
   2 | Sessel
   3 | Spaten
   4 | Stehtisch
(4 rows)

test=*# select * from produktkategorie ;
  pid  | kid | hk
-------+-----+----
 61101 |   1 | t
 61101 |   4 | f
 63102 |   3 | f
(3 rows)

test=*# select p.pid, p.bez, string_agg(case when pk.hk then k.bez else null end,'') as haupt, string_agg(case when not pk.hk then k.bez else null end,'') as neben from produktkategorie pk left join produkt p on pk.pid=p.pid left join kategorie k on pk.kid=k.kid group by p.pid,p.bez;
  pid  |        bez         | haupt  |   neben   
-------+--------------------+--------+-----------
 61101 | Picknicktisch Bank | Tische | Stehtisch
 63102 | Klappspaten        |        | Spaten
(2 rows)

test=*#

iCrystal · 28 Juni 2020

akretschmer schrieb:

Bist recht hilflos, oder? Kann man sich da nicht schnell auf dem privaten Laptop ein schönes PostgreSQL installieren und testen? Müssen Deine Helfer alles machen?

Code:

test=*# select * from produkt;
  pid  |        bez        
-------+--------------------
 61101 | Picknicktisch Bank
 61102 | Larum Fresstisch
 63102 | Klappspaten
(3 rows)

test=*# select * from kategorie ;
 kid |    bez  
-----+-----------
   1 | Tische
   2 | Sessel
   3 | Spaten
   4 | Stehtisch
(4 rows)

test=*# select * from produktkategorie ;
  pid  | kid | hk
-------+-----+----
 61101 |   1 | t
 61101 |   4 | f
 63102 |   3 | f
(3 rows)

test=*# select p.pid, p.bez, string_agg(case when pk.hk then k.bez else null end,'') as haupt, string_agg(case when not pk.hk then k.bez else null end,'') as neben from produktkategorie pk left join produkt p on pk.pid=p.pid left join kategorie k on pk.kid=k.kid group by p.pid,p.bez;
  pid  |        bez         | haupt  |   neben  
-------+--------------------+--------+-----------
 61101 | Picknicktisch Bank | Tische | Stehtisch
 63102 | Klappspaten        |        | Spaten
(2 rows)

test=*#

Wenn wir mit PostgreSQL arbeiten würden, dann würde ich es auch im bestimmten Abschnitt danach fragen oder?
Brauchst nicht gleich so zu antworten. Bist du als ein Profi geboren oder was? Bestimmt hast du auch damals einige Fragen gestellt oder nicht, damit deine "Helfer" zur Einsatz kommen?

akretschmer · 28 Juni 2020

iCrystal schrieb:
Bestimmt hast du auch damals einige Fragen gestellt oder nicht, damit deine "Helfer" zur Einsatz kommen?

Das war für Dich als Hilfe bestimmt. Um es kurz zu sagen: helfe uns, Dir zu helfen!

ukulele · 29 Juni 2020

Bist du sicher mit den maximal 2 Kategorien? Das Tabellendesign gibt nämlich 1 bis n Zuordnungen her, es müsste also anderweitig das Maximum von 2 vorgegeben werden.

Hier mal ein Ansatz der Funktioniert aber eine 3te Kategorie ggf. ignoriert:

Code:

WITH t AS (
SELECT ROW_NUMBER() OVER (GROUP BY z.PID ORDER BY k.HK,k.BEZ) AS zeile,z.PID,z.KID,k.BEZ
FROM produktkategorie z
INNER JOIN kategorie k ON z.KID = k.ID )
SELECT p.*,t1.BEZ AS kategorie1, t2.BEZ AS kategorie2
FROM produkte p
LEFT JOIN t t1 ON p.ID = t.PID AND t.zeile = 1
LEFT JOIN t t2 ON p.ID = t.PID AND t.zeile = 2

Eine Zeile darstellen mit zwei Fremdschlüssel

iCrystal

Benutzer

akretschmer

Datenbank-Guru

iCrystal

Benutzer

iCrystal

Benutzer

akretschmer

Datenbank-Guru

iCrystal

Benutzer

akretschmer

Datenbank-Guru

ukulele

Datenbank-Guru