Gleiches JOIN für mehrere Spalten

[SaJu]

Hallo,

ich brauche eine SELECT-Abfrage für unsere Jira-Datenbank. Dabei müssen die kurzen Namen in die vollen Namen umgewandelt werden.

Dafür verwende ich folgende Tabellen:

mysql> show fields from jiraissue;+----------------------+---------------+------+-----+---------+-------+

| Field                | Type          | Null | Key | Default | Extra |

+----------------------+---------------+------+-----+---------+-------+

| ID                   | decimal(18,0) | NO   | PRI | NULL    |       |

| pkey                 | varchar(255)  | YES  |     | NULL    |       |

| issuenum             | decimal(18,0) | YES  | MUL | NULL    |       |

| PROJECT              | decimal(18,0) | YES  | MUL | NULL    |       |

| REPORTER             | varchar(255)  | YES  |     | NULL    |       |

| ASSIGNEE             | varchar(255)  | YES  | MUL | NULL    |       |

| CREATOR              | varchar(255)  | YES  |     | NULL    |       |

| issuetype            | varchar(255)  | YES  |     | NULL    |       |

| SUMMARY              | varchar(255)  | YES  |     | NULL    |       |

| DESCRIPTION          | longtext      | YES  |     | NULL    |       |

| ENVIRONMENT          | longtext      | YES  |     | NULL    |       |

| PRIORITY             | varchar(255)  | YES  |     | NULL    |       |

| RESOLUTION           | varchar(255)  | YES  |     | NULL    |       |

| issuestatus          | varchar(255)  | YES  |     | NULL    |       |

| CREATED              | datetime      | YES  |     | NULL    |       |

| UPDATED              | datetime      | YES  | MUL | NULL    |       |

| DUEDATE              | datetime      | YES  |     | NULL    |       |

| RESOLUTIONDATE       | datetime      | YES  |     | NULL    |       |

| VOTES                | decimal(18,0) | YES  |     | NULL    |       |

| WATCHES              | decimal(18,0) | YES  |     | NULL    |       |

| TIMEORIGINALESTIMATE | decimal(18,0) | YES  |     | NULL    |       |

| TIMEESTIMATE         | decimal(18,0) | YES  |     | NULL    |       |

| TIMESPENT            | decimal(18,0) | YES  |     | NULL    |       |

| WORKFLOW_ID          | decimal(18,0) | YES  | MUL | NULL    |       |

| SECURITY             | decimal(18,0) | YES  |     | NULL    |       |

| FIXFOR               | decimal(18,0) | YES  |     | NULL    |       |

| COMPONENT            | decimal(18,0) | YES  |     | NULL    |       |

+----------------------+---------------+------+-----+---------+-------+

27 rows in set (0.01 sec)

und

mysql> show fields from cwd_user;

+---------------------+---------------+------+-----+---------+-------+

| Field               | Type          | Null | Key | Default | Extra |

+---------------------+---------------+------+-----+---------+-------+

| ID                  | decimal(18,0) | NO   | PRI | NULL    |       |

| directory_id        | decimal(18,0) | YES  |     | NULL    |       |

| user_name           | varchar(255)  | YES  |     | NULL    |       |

| lower_user_name     | varchar(255)  | YES  | MUL | NULL    |       |

| active              | decimal(9,0)  | YES  |     | NULL    |       |

| created_date        | datetime      | YES  |     | NULL    |       |

| updated_date        | datetime      | YES  |     | NULL    |       |

| first_name          | varchar(255)  | YES  |     | NULL    |       |

| lower_first_name    | varchar(255)  | YES  | MUL | NULL    |       |

| last_name           | varchar(255)  | YES  |     | NULL    |       |

| lower_last_name     | varchar(255)  | YES  | MUL | NULL    |       |

| display_name        | varchar(255)  | YES  |     | NULL    |       |

| lower_display_name  | varchar(255)  | YES  | MUL | NULL    |       |

| email_address       | varchar(255)  | YES  |     | NULL    |       |

| lower_email_address | varchar(255)  | YES  | MUL | NULL    |       |

| CREDENTIAL          | varchar(255)  | YES  |     | NULL    |       |

| deleted_externally  | decimal(9,0)  | YES  |     | NULL    |       |

| EXTERNAL_ID         | varchar(255)  | YES  | MUL | NULL    |       |

+---------------------+---------------+------+-----+---------+-------+

18 rows in set (0.00 sec)

jiraissue soll fast vollständig ausgegeben werden. Aus cwd_user muss display_name für die 3 Personen verwendet werden.

lower_user_name = REPORTER, ASSIGNEE, CREATOR

Aber wie bekomme ich das in einen SELECT-Befehl?

Vielen Dank für die Unterstützung!

Bearbeitet 12. Februar 2014 von SaJu

[Aras]

Da musst du mMn drei Joins machen.

Select jiraissue.*, reporterjoin.display_name as reportername,assigneejoin.display_name as assigneename,createjoin.display_name as creatorname from jiraissue

JOIN cwd_user as reporterjoin

ON jiraissue.REPORTER = cwd_user.lower_user_name

JOIN cwd_user as assigneejoin

ON jiraissue.CREATOR = cwd_user.lower_user_name

JOIN cwd_user as createjoin

ON jiraissue.CREATOR = cwd_user.lower_user_name

[SaJu]

Danke!

Allerdings wirft das folgenden Fehler:

ERROR 1054 (42S22): Unknown column 'cwd_user.lower_user_name' in 'on clause'

Zuerst dachte ich, dass die Datenbank lower_user_name nicht findet. Dann habe ich über stackoverflow heraus gefunden, dass damit mehrere JOIN-Stile gemischt werden, was nicht erlaubt ist.

[Aras]

Select jiraissue.*, reporterjoin.display_name as reportername,assigneejoin.display_name as assigneename,createjoin.display_name as creatorname from jiraissue

JOIN cwd_user as reporterjoin

ON jiraissue.REPORTER = reporterjoin.lower_user_name

JOIN cwd_user as assigneejoin

ON jiraissue.CREATOR = assigneejoin.lower_user_name

JOIN cwd_user as createjoin

ON jiraissue.CREATOR = createjoin.lower_user_name

Vielleicht klappt es so

[SaJu]

Super! Danke. Das funktioniert.