PostgreSQL JOIN mit LIKE - Syntax Hilfe?

[slu]

Hallo,

ich versuche an einer PostgreSQL ein SELECT mit JOIN und LIKE, das ist aber nicht so einfach wie ich gerade feststelle.

Hab das ganze mal lesbar gekürzt, vielleicht weiß jemand wie das geht:

Code: Alles auswählen

SELECT name, preisliste.preis, code, status FROM produkt
INNER JOIN preisliste.verweis ON produkt.id LIKE '%produkt.id'
WHERE produkt.status = TRUE

Das Problem an der Sache ist das in der Tabelle preisliste.verweis nicht einfach die (db.)id vom Produkt steht sondern:
"kundenpreis,id" also z.B. "kundenpreis,134".

Einfache JOIN mit ein paar WHERE sind für mich kein Problem, aber mit so einer Aufgabe tue ich mich schwer.
Vielleicht fällt das jemand leichter und hat sogar eine Lösung für mich?

[shoening]

Hi,

von meinem Verständnis bzgl. JOIN gibt man in der ON Klausel immer Primary bzw. Unique Key Spalten an.

Was Du haben willst ist ein Join den man (OHNE JOIN KLAUSEL) nur mittels WHERE Klausel hinbekommen sollte.

Oder Du machtst für eine der "Tabellen" ein Subselect, so dass Du die Felder doch direkt vergleichen kannst.

Also statt "produkt" ein "(select 'kundenpreis'||id as _id, name, code, status FROM produkt) as _produkt" und dann ein "ON _product._id = preisliste.verweis"

(Ich hoffe, ich habe mich klar genug ausgedrückt).

Viele Grüße
Stefan

[slu]

Hallo Stefan,

danke für dein Beitrag und das rein denken.

Ich komme nicht ganz mit, woher weiß der SELECT wie er die id verbinden muss?
In dem Feld steht leider immer "EIGENSCHAFT,ID", also "kundenpreis,134", es kann z.B. für das gleiche Produkt den Eintrag "einkaufspreis,134" geben.

Mich interessiert aber nur die Eigenschaft "kundenpreis", es könnte so einfach sein wenn die ids matchen wie in fast allen anderen Tabellen der Anwendung auch...

[heisenberg]

Vielleicht eher so etwas (geht bei mir):

Code: Alles auswählen

SELECT produkt.name, produkt.code, produkt.status, preisliste.preis 
FROM produkt, preisliste 
WHERE preisliste.verweis LIKE CONCAT('%,',produkt.id)
      AND status = TRUE;

Alternativer Ansatz: Man kann sich da bestimmt eine View basteln, die die Felder entsprechend zerlegt.

Nachtrag:

Ich habe jetzt erst verstanden, dass in dem Verweis-Feld nicht der Kundenpreis drin steht, sondern das Literal "kundenpreis" und alternativ das Literal "einkaufspreis", wobei Dich aber letzteres nicht interessiert.

Also wäre die Abfrage:

Code: Alles auswählen

SELECT produkt.name, produkt.code, produkt.status, preisliste.preis 
FROM produkt, preisliste 
WHERE preisliste.verweis = CONCAT('kundenpreis,',produkt.id)
      AND status = TRUE;

[heisenberg]

Hier auch nochmal ein Beispiel der View:

Meine Ausgangsdaten:

Code: Alles auswählen

test=# \d produkt
                          Tabelle »public.produkt«
 Spalte |          Typ          | Sortierfolge | NULL erlaubt? | Vorgabewert 
--------+-----------------------+--------------+---------------+-------------
 name   | character varying(20) |              |               | 
 id     | integer               |              |               | 
 status | boolean               |              |               | 
 code   | character varying(20) |              |               | 

test=# select * from produkt;

  name   | id | status | code 
---------+----+--------+------
 bananen |  1 | t      | BANA
 aepfel  |  3 | t      | APPL
 trauben |  4 | t      | TRAU
 orangen |  2 | f      | ORAN
(4 Zeilen)

test=# \d preisliste
                         Tabelle »public.preisliste«
 Spalte  |          Typ          | Sortierfolge | NULL erlaubt? | Vorgabewert 
---------+-----------------------+--------------+---------------+-------------
 verweis | character varying(50) |              |               | 
 preis   | double precision      |              |               | 

test=# select * from preisliste;
     verweis     | preis 
-----------------+-------
 kundenpreis,1   |    20
 kundenpreis,2   |    19
 kundenpreis,3   |     5
 einkaufspreis,4 |    11
(4 Zeilen)

Eine View würde ich dann so anlegen:

Code: Alles auswählen

CREATE VIEW preisliste_separiert AS 
     SELECT 
        SPLIT_PART(verweis,',',1) AS preis_typ,
        CAST(SPLIT_PART(verweis,',',2) AS INT) AS produkt_id,
        preis 
     FROM preisliste;

Die CAST-Funktion wandelt den String in eine Zahl um, damit die Datentypen der IDs jeweils beide gleich (hier INT) sind.

Mit dem Ergebnis:

Code: Alles auswählen

SELECT * FROM preisliste_separiert;

   preis_typ   | produkt_id | preis 
---------------+------------+-------
 kundenpreis   | 1          |    20
 kundenpreis   | 2          |    19
 kundenpreis   | 3          |     5
 einkaufspreis | 4          |    11
(4 Zeilen)

Dann kann man da wunderbar den JOIN darauf ausführen:

Code: Alles auswählen

SELECT produkt.name, produkt.code, produkt.status, preisliste_separiert.preis  
FROM produkt
INNER JOIN 
   preisliste_separiert ON 
        produkt.status = TRUE
        AND preisliste_separiert.preis_typ = 'kundenpreis'
   	AND produkt.id = preisliste_separiert.produkt_id;

  name   | code | status | preis 
---------+------+--------+-------
 bananen | BANA | t      |    20
 aepfel  | APPL | t      |     5
(2 Zeilen)

Ich gehe davon aus, dass die Reihenfolge der Ausdrücke für die Performance wichtig ist. Die einfachen Ausdrücke zuerst und zum Schluß dann die Ausdrücke, bei denen die Daten beider Tabellen zu vergleichen sind.

Hier auch nochmal der Dump der kleinen Datenbank: 42049

[Meillo]

Das eigentliche Problem ruehrt daher, dass das Datenbankdesign schlecht ist. Anstatt sich damit einen abzubrechen, ist es sinnvoller, das Design zu aendern. Wenn es nicht moeglich ist, die Tabellen zu aendern, dann finde ich den Vorschlag von heisenberg sinnvoll. Der erzeugt aus der schlechten Tabelle eine View, die dann so genutzt werden kann, wie das fuer SQL gedacht ist.

Rob Pike hat das immer stark vertreten: Programmiere mit intelligenten Datenstrukturen und dummem Code. -- Das ist eine gute Empfehlung hier: Stecke die Intelligenz in den Versuch, die Datenstrukturen (=Tabellen) zu verbessern und schreibe dann stinknormales SQL. Die View ist dieser intelligente Vorschlag.

[paedubucher]

Rob Pike hat das immer stark vertreten: Programmiere mit intelligenten Datenstrukturen und dummem Code. -- Das ist eine gute Empfehlung hier: Stecke die Intelligenz in den Versuch, die Datenstrukturen (=Tabellen) zu verbessern und schreibe dann stinknormales SQL. Die View ist dieser intelligente Vorschlag.

Linus Torvalds schlägt in die gleiche Kerbe:

"Bad programmers worry about the code. Good programmers worry about data structures and their relationships."

Anhand von SQL kann man das noch weitertreiben: Entweder ist die Abfrage offensichtlich oder etwas ist falsch.

Sogenannte Workflow Engines, wo eine Software anhand eines Ablaufs und nicht anhand der Datenstrukturen designed wird, sind eine gewaltige Regression in der Softwareentwicklung.

[slu]

Vielen Dank für eure Beiträge, ich werde mich daran am Montag versuchen.

Eine View würde ich dann so anlegen:

Kann ich ein View anlegen wenn der PostgreSQL Benutzer nur SELECT Rechte hat?
(Ich kann nur lesend auf die Datenbank zugreifen).

Das eigentliche Problem ruehrt daher, dass das Datenbankdesign schlecht ist.

Das stimmt, nur leider kann ich das nicht ändern.
Bis zu diesem einen SELECT an dem ich jetzt hänge bin ich super mit Abfragen zurecht gekommen, aber diese Tabelle ist wirklich eine Herausforderung für mich.

[Meillo]

Vielen Dank für eure Beiträge, ich werde mich daran am Montag versuchen.

Eine View würde ich dann so anlegen:

Kann ich ein View anlegen wenn der PostgreSQL Benutzer nur SELECT Rechte hat?
(Ich kann nur lesend auf die Datenbank zugreifen).

An sich ist eine View nur eine wiederholt ausgefuehrte Select-Abfrage.

The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.

... damit sollten nicht viel mehr Rechte noetig sein.

Was aber genau fuer Rechte noetig sind, ist mir aus der Dokumentation nicht so ganz klar geworden: https://www.postgresql.org/docs/current ... eview.html

Du kannst es ja einfach ausprobieren.

Jedenfalls brauchst du keine Rechte, mit denen du etwas kaputt machen koenntest. Falls du noch weitere Rechte brauchst, die keine Daten veraendern koennen, koenntest du dir diese noch geben lassen.

[heisenberg]

Man braucht das CREATE - Recht für das entsprechende Schema der Datenbank. Dort sind Views als Objekte angelegt. Üblicherweise gibt es nur eines: "Public"

Siehe:

https://www.postgresqltutorial.com/post ... ql-schema/

https://www.postgresql.org/docs/current/sql-grant.html

Alternative ist, statt einer View einen Subselect zu verwenden statt dem Namen der View - und zwar genau den SELECT, der bei CREATE VIEW oben steht:

Also statt ...

Code: Alles auswählen

SELECT * FROM preisliste_separiert;

so ...

Code: Alles auswählen

SELECT * FROM (
    SELECT 
        SPLIT_PART(verweis,',',1) AS preis_typ,
        CAST(SPLIT_PART(verweis,',',2) AS INT) AS produkt_id,
        preis 
     FROM preisliste);
     

Das verlängert das JOIN-Statement natürlich beträchtlich, auch wenn das faktisch die gleiche Abfrage ist.

Programmatisch kann man den Subselect ja in eine Variable packen. Dann sieht's schon nicht mehr so schlimm aus.

Code: Alles auswählen

#!/bin/bash

PREISLISTE_SEPARIERT="SELECT 
        SPLIT_PART(verweis,',',1) AS preis_typ,
        CAST(SPLIT_PART(verweis,',',2) AS INT) AS produkt_id,
        preis 
     FROM preisliste"
     
SQL="SELECT * FROM ($PREISLISTE_SEPARIERT);"
     

[slu]

Vielen dank, der SELECT funktioniert und liefert mir genau das gewünschte Ergebnis!

Ihr seit einfach super!