Join von 2 DB

[scheffer]

Hallo,
ich versuche zwei Tabellen über ein Join abzufragen, leider gelingt mir das nur bedingt.

Hier mal die Infos zu den Tabellen:
Tabelle 1: x_users mit "id, name, username, email"
Tabelle 2: x_fields_values mit "field_id, item_id, value"

Die Felder x_users.id und x_fields_values.item_id sind die die IDs der User, über die die Abfrage generiert wird.
x_fields_value.field_id kann die Werte 1-17, wobei die die x_fields_value.value dann z.B. Vorname, Strasse, etc hat

Hier ein Beispiel:
Tabelle 1:
id name username email
1 Müller mueller mueller@gmx.de
2 Schmidt schmidt schmidt@gmx.de

Tabelle 2:
field_id item_id value
12 1 Peter
13 1 Musterstrasse
1 1 089-12345678
12 2 Monika
13 2 Ortgasse
1 2 089-9876543

Die Abfrage mache ich über:
$sql = 'SELECT * FROM x_users INNER JOIN x_fields_values ON (x_users.id = x_fields_values.item_id) ORDER BY x_users.username ASC';
und lasse mir dann in einer Tabelle die Ergenisse anzeigen:

echo '<table border="1">';
while ($row = $erg->fetch_assoc()) {
// echo '"'.$row['id'].'" "'.$row['name'].'" "'.$row['username'].'" "'.$row['email']."\"<br />\n";
echo "<tr>";
echo "<td>". $row['id'] . "</td>";
echo "<td>". $row['name'] . "</td>";
echo "<td>". $row['username'] . "</td>";
echo "<td>". $row['email'] . "</td>";
echo "<td>". $row['value'] . "</td>";
echo "</tr>";
}
echo "</table>";

Das bewirkt aber, dass die Tabelle wir folgt aussieht:
1 Müller mueller mueller@gmx.de Peter
1 Müller mueller mueller@gmx.de Musterstrasse
1 Müller mueller mueller@gmx.de 089-12345678
2 Schmidt schmidt schmidt@gmx.de Monika
2 Schmidt schmidt schmidt@gmx.de Monika
2 Schmidt schmidt schmidt@gmx.de 089-9876543

Ich hätte aber gerne:
1 Müller mueller mueller@gmx.de Peter Musterstrasse 089-12345678
2 Schmidt schmidt schmidt@gmx.de Monika Ortgasse 089-9876543


Bin für jeden Tipp dankbar.


VG
rs

 

[akretschmer]

test=*# select * from x_users ;
 id |  name   | username |     email     
----+---------+----------+----------------
  1 | müller  | mueller  | mueller@gmx.de
  2 | schmidt | schmidt  | schmidt@gmx.de
(2 rows)

test=*# select * from x_fields_values ;
 field_id | item_id |    value     
----------+---------+--------------
       12 |       1 | peter
       13 |       1 | musterstraße
        1 |       1 | 08912345678
       12 |       2 | monika
       13 |       2 | ortsgasse
        1 |       2 | 0899876543
(6 rows)

test=*# select u.name, u.username, u.email, max(i.value) filter (where i.field_id = 12) as vorname,max(i.value) filter (where i.field_id = 13) as straße, max(i.value) filter (where i.field_id = 1) as telefon from x_users u left join x_fields_values i on (u.id=i.item_id) group by u.name, u.username, u.email;
  name   | username |     email      | vorname |    straße    |   telefon   
---------+----------+----------------+---------+--------------+-------------
 schmidt | schmidt  | schmidt@gmx.de | monika  | ortsgasse    | 0899876543
 müller  | mueller  | mueller@gmx.de | peter   | musterstraße | 08912345678
(2 rows)

test=*#

PostgreSQL, für MySQL wirst das etwas anpassen müssen.

 

[akretschmer]

Hier noch eine beim Feld telefon leicht andere Variante:

test=*# select u.name, u.username, u.email, max(i.value) filter (where i.field_id = 12) as vorname,max(i.value) filter (where i.field_id = 13) as straße, string_agg(case when i.field_id=1 then i.value else null end,'') as telefon from x_users u left join x_fields_values i on (u.id=i.item_id) group by u.name, u.username, u.email;
  name   | username |     email      | vorname |    straße    |   telefon   
---------+----------+----------------+---------+--------------+-------------
 schmidt | schmidt  | schmidt@gmx.de | monika  | ortsgasse    | 0899876543
 müller  | mueller  | mueller@gmx.de | peter   | musterstraße | 08912345678
(2 rows)

test=*#

Gibt also, wie so oft, mehrere Möglichkeiten.

 

[scheffer]

test=*# select * from x_users ;
 id |  name   | username |     email    
----+---------+----------+----------------
  1 | müller  | mueller  | mueller@gmx.de
  2 | schmidt | schmidt  | schmidt@gmx.de
(2 rows)

test=*# select * from x_fields_values ;
 field_id | item_id |    value    
----------+---------+--------------
       12 |       1 | peter
       13 |       1 | musterstraße
        1 |       1 | 08912345678
       12 |       2 | monika
       13 |       2 | ortsgasse
        1 |       2 | 0899876543
(6 rows)

test=*# select u.name, u.username, u.email, max(i.value) filter (where i.field_id = 12) as vorname,max(i.value) filter (where i.field_id = 13) as straße, max(i.value) filter (where i.field_id = 1) as telefon from x_users u left join x_fields_values i on (u.id=i.item_id) group by u.name, u.username, u.email;
  name   | username |     email      | vorname |    straße    |   telefon  
---------+----------+----------------+---------+--------------+-------------
 schmidt | schmidt  | schmidt@gmx.de | monika  | ortsgasse    | 0899876543
 müller  | mueller  | mueller@gmx.de | peter   | musterstraße | 08912345678
(2 rows)

test=*#

PostgreSQL, für MySQL wirst das etwas anpassen müssen.

Vielen lieben Dank für die schnelle Hilfe. Leider führt das bei mir zu einem Fehler:

Meldung:
Etwas stimmte mit dem Query nicht: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(where i.field_id = 12) as vorname,max(i.value) filter (where i.field_id = 13) a' at line 1
Ich nutze mein Testsystem unter MAMP (auf MAC) mit MySQL-Version: 5.7.26
In echt läuft das System auf Betriebssystem: MySQL-Version: 5.6.42-log
Du hast geschrieben: PostgreSQL, für MySQL wirst das etwas anpassen müssen.

Könntest Du mir bitte einen Tipp geben, was ich für MySQL ändern muss ?

Danke und VG
rs

 

[akretschmer]

Also generell gesprochen funktioniert MySQL deutlich besser, wenn man es durch PostgreSQL ersetzt ;-)

Die zweite Variante, also das "string_agg(case when i.field_id=1 then i.value else null end,'') as telefon", sollte sich mit MySQL einfacher machen lassen als die sehr elegante Variante mit FILTER ( ... ).

 

[akretschmer]

Hint: string_agg() ist eine Aggregatsfunktion. Es gibt was ähnliches in MySQL.