INNER JOIN drie tabellen
Hieronder de wat ik nu heb. (uiteraard niet werkend).. de HAM vraag is hoe gaat het me lukken dat ik het wel werkend krijg? Kan iemand mij helpen hiermee aub?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
function person2role (){
global $connection;
$where = NULL;
if (isset($_GET['asn'])){
$where = 'MSKEYVALUE_MEDEWERKER = "'.mysqli_real_escape_string($connection, $_GET['asn']).'"';
}
$sqlUitlezen = mysqli_query($connection, $sql = "
SELECT
person.*,
role.*,
person2role.*
FROM
person
INNER JOIN
person2role
ON
person.MSKEYVALUE_MEDEWERKER = person2role.MSKEYVALUE_MEDEWERKER
INNER JOIN
role
ON
person2role.MSKEYVALUE_ROL = role.MSKEYVALUE_ROL
WHERE
".$where."
");
echo '<b>$where:</b> '.$where;
echo '<br>';
echo '<b>$sql :</b> '.$sql;
$sqlAantal = mysqli_num_rows($sqlUitlezen);
if ($sqlAantal > 0){
echo '<article>';
echo '<table id="customers">';
echo ' <tr>';
echo ' <td colspan="4" align="right"><h3>Aantal medewerkers binnen deze OU ('.$sqlAantal.') ';
echo ' </tr>';
echo ' <tr>';
echo ' <th><h4>ASN</h4></th>';
echo ' <th><h4>Rol</h4></th>';
echo ' <th colspan="2" nowrap><h4>Geldig tot</h4></th>';
echo ' </tr>';
while ($sqlData = mysqli_fetch_assoc($sqlUitlezen)){
echo ' <tr>';
echo ' <td><h6>'.$sqlData['MSKEYVALUE_MEDEWERKER'].'</h6></td>';
echo ' <td><h6>x</h6></td>';
echo ' <td><h6>x</h6></td>';
echo ' <td align="center">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<!--
<td><img src="../img/icon-rollen.png" height="20" title="Rollen"></td>
-->
<td><A HREF="javascript:javascript:history.go(-1)"><img src="../img/icon-pagina-terug.png" height="12" title="Vorige pagina"></a></td>
</tr>
</table>
</td>';
echo ' </tr>';
}
echo '</table>';
echo '</article>';
}else{
echo '<h4>Sorry, ik kan geen medewerker-rolkoppeling vinden!</h4>';
}
}
global $connection;
$where = NULL;
if (isset($_GET['asn'])){
$where = 'MSKEYVALUE_MEDEWERKER = "'.mysqli_real_escape_string($connection, $_GET['asn']).'"';
}
$sqlUitlezen = mysqli_query($connection, $sql = "
SELECT
person.*,
role.*,
person2role.*
FROM
person
INNER JOIN
person2role
ON
person.MSKEYVALUE_MEDEWERKER = person2role.MSKEYVALUE_MEDEWERKER
INNER JOIN
role
ON
person2role.MSKEYVALUE_ROL = role.MSKEYVALUE_ROL
WHERE
".$where."
");
echo '<b>$where:</b> '.$where;
echo '<br>';
echo '<b>$sql :</b> '.$sql;
$sqlAantal = mysqli_num_rows($sqlUitlezen);
if ($sqlAantal > 0){
echo '<article>';
echo '<table id="customers">';
echo ' <tr>';
echo ' <td colspan="4" align="right"><h3>Aantal medewerkers binnen deze OU ('.$sqlAantal.') ';
echo ' </tr>';
echo ' <tr>';
echo ' <th><h4>ASN</h4></th>';
echo ' <th><h4>Rol</h4></th>';
echo ' <th colspan="2" nowrap><h4>Geldig tot</h4></th>';
echo ' </tr>';
while ($sqlData = mysqli_fetch_assoc($sqlUitlezen)){
echo ' <tr>';
echo ' <td><h6>'.$sqlData['MSKEYVALUE_MEDEWERKER'].'</h6></td>';
echo ' <td><h6>x</h6></td>';
echo ' <td><h6>x</h6></td>';
echo ' <td align="center">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<!--
<td><img src="../img/icon-rollen.png" height="20" title="Rollen"></td>
-->
<td><A HREF="javascript:javascript:history.go(-1)"><img src="../img/icon-pagina-terug.png" height="12" title="Vorige pagina"></a></td>
</tr>
</table>
</td>';
echo ' </tr>';
}
echo '</table>';
echo '</article>';
}else{
echo '<h4>Sorry, ik kan geen medewerker-rolkoppeling vinden!</h4>';
}
}
Alvast bedankt voor het meedenken.
Laat anders even een uitvoer van MySQL zien.
En inderdaad, wat verwacht je dat er zou moeten gebeuren en wat gebeurt er daadwerkelijk en hoe verschilt dit van elkaar.
Ik zal een poging ondernemen ;-)
Wat ik als gewenste resultaat zo willen dat ik van persoon (willekeurig geselecteerd uit een lijst op basis van ASN (dit is een uniek nummer... je kan het vergelijk met een BSN nr die we allemaal hebben).
Wanneer ik de op die persoon klik dan ziet de link er als volgt uit:
http://000.000.000.00/bp/members/index.php?actie=person2role&asn=100.005.813
Ik roep dan de functie person2role (die hierboven staat) aan waarbij criteria dat asn is.
Ik zou verwachten dat ik dan een overzicht krijgt van die persoon (tabel person) met welke rollen (tabel role) via het koppeltabel (person2role) in bezit heeft.
de velden waarop gematched kan worden is:
tabel person:
MSKEYVALUE_MEDEWERKER
tabel person2role:
MSKEYVALUE_MEDEWERKER en
MSKEYVALUE_ROL
tabel role:
MSKEYVALUE_ROL
Naar mijn idee zou het moeten lukken om een overzicht te genereren. Maar dit is voor nog ietwat aan de hoge kant blijkt.. In ieder geval ik kom er na 2 avondjes puzzelen nog niet tot het gewenste resultaat.
Ik denk dat ik het redelijk heb omschreven... misschien ook niet niet maar dat verneem ik dan graag.
Wederom een bedankje op zijn plaats voor de harde meedenkers
Nog ter aanvulling... wanneer ik echo $Where; an en echo $sql; uitvoer is krijgt ik het volgende:
$where: MSKEYVALUE_MEDEWERKER = "100.005.813"
$sql : SELECT person.MSKEYVALUE_MEDEWERKER FROM person INNER JOIN person2role ON person.MSKEYVALUE_MEDEWERKER = person2role.MSKEYVALUE_MEDEWERKER WHERE MSKEYVALUE_MEDEWERKER = "100.005.813"
Gewijzigd op 05/02/2019 19:59:25 door - DHU -
Je functie doet daarnaast veel te veel: person2role() zou person2role($asn) met een parameter voor de ID moeten zijn, om te beginnen.
Ward van der Put op 05/02/2019 18:55:46:
Als je de persoon al weet en je alleen zijn/haar rollen wilt weten, moet je de query uitvoeren vanuit de tabel person2role.
Je functie doet daarnaast veel te veel: person2role() zou person2role($asn) met een parameter voor de ID moeten zijn, om te beginnen.
Je functie doet daarnaast veel te veel: person2role() zou person2role($asn) met een parameter voor de ID moeten zijn, om te beginnen.
Hoi Ward... klopt... uiteraard wil ik nog wel aanvullende gegevens uit de tabel person trekken wanneer eenmaal de verbinding ligt.
Waar strandt het schip precies?
En wat @Ward zegt, indien je hier een functie van bakt zorg dan dat deze herbruikbaar is (gebruik parameters). Mogelijk zou deze functie eigenlijk alleen een array met data moeten teruggeven, het weergeven hiervan is een aparte taak die ergens anders -met behulp van deze functie- uitgevoerd kan worden.
Met onderstaande code krijg ik een overzicht welke persoon welke rollen heeft.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
function person2role (){
global $connection;
$where = NULL;
if (isset($_GET['asn'])){
$where = 'WHERE person.MSKEYVALUE_MEDEWERKER = "'.mysqli_real_escape_string($connection, $_GET['asn']).'"';
}
$sqlUitlezen = mysqli_query($connection, $sql = "
SELECT
person.*,
person2role.*
FROM
person
LEFT JOIN
person2role
ON
person.MSKEYVALUE_MEDEWERKER = person2role.MSKEYVALUE_MEDEWERKER
".$where."
ORDER BY
person.MSKEYVALUE_MEDEWERKER ASC
");
global $connection;
$where = NULL;
if (isset($_GET['asn'])){
$where = 'WHERE person.MSKEYVALUE_MEDEWERKER = "'.mysqli_real_escape_string($connection, $_GET['asn']).'"';
}
$sqlUitlezen = mysqli_query($connection, $sql = "
SELECT
person.*,
person2role.*
FROM
person
LEFT JOIN
person2role
ON
person.MSKEYVALUE_MEDEWERKER = person2role.MSKEYVALUE_MEDEWERKER
".$where."
ORDER BY
person.MSKEYVALUE_MEDEWERKER ASC
");
maar hiermee kan ik dus geen aanvullende informatie halen want de 3e tabel `role` ontbreekt. Het lukt me niet om een 2 JOIN toe te voegen want dan krijg ik de foutmelding die hierboven al zijn beschreven.
Thomas tipt daar nog een goed punt aan. Er zullen personen zijn die geen rol hebben gekoppeld. Maar hoe krijg ik die inzichtelijk. Is dat een andere query of kan/moet dat in deze mee worden genomen?
Een persoon heeft 0 of meer rollen. Klopt dit?
Je gebruikt het ASN ook als intern gebruikers-id, ik hoop dat je je database ook echt relationeel hebt opgezet?
Normaal gesproken wordt het personeelsnummer (ASN) vaak nog vertaald naar een soort van intern gebruikers-id, wat gewoon een auto-increment veldje (oplopend nummer) is. Dit lijkt mij voor indexering (levert je snellere queries op) en interne consistentie beter. Maar goed. Als je overal dat ASN gebruikt dan kun je in principe person2role als uitgangspunt nemen... ware het niet dat het ook voor kan komen dat iemand geen rol heeft. Dus voor nu maar person als uitgangspunt.
Snap je het verschil tussen een LEFT JOIN en een INNER JOIN?
person2role bevat in principe geen nieuwe informatie, dit is slechts een koppeltabel, dus daar hoef je geen informatie uit op te halen lijkt mij? Wat heb je nodig uit de role-tabel? Zijn dit enkel de labels van de concrete rollen? Volgens mij kom je een eind als je zoiets doet:
SELECT <informatie die je wilt hebben>
FROM person
LEFT JOIN person2role ON person2role.MSKEYVALUE_MEDEWERKER = person.MSKEYVALUE_MEDEWERKER
LEFT JOIN role ON role.MSKEYVALUE_ROL = person2role.MSKEYVALUE_ROL
WHERE person.MSKEYVALUE_MEDEWERKER = <ASN>
Een LEFT JOIN op person2role en role omdat het niet gegarandeerd is dat iedereen een rol heeft.
Vervolgens zal je in een loopje de resultaten moeten uitlezen waarbij je moet controleren of informatie in person2role (en role) verschilt van NULL - het kan namelijk voorkomen dat iemand geen rol toegewezen heeft gekregen. De LEFT JOIN zorgt er dan voor dat wel informatie over de desbetreffende persoon wordt opgehaald, maar de ontbrekende informatie (in person2role en role) wordt bij afwezigheid opgevuld met NULL-waarden. Zou je een INNER JOIN gebruiken krijg je de person niet als resultaat retour.
Je zou ook nog iets met GROUP_CONCAT kunnen doen als je dat leuker vindt.
Bijbehorende testjes:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
# gebruikers tabel
CREATE TABLE test_a (
a_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
a_text VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# koppeltabel
CREATE TABLE test_a_b (
a_id INT(10) UNSIGNED NOT NULL,
b_id INT(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# rechten tabel
CREATE TABLE test_b (
b_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
b_text VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# relaties tussen de tabellen, verwijder corresponderende gebruiker of recht als tegenhanger verdwijnt
ALTER TABLE test_a_b ADD FOREIGN KEY (a_id) REFERENCES test_a(a_id) ON DELETE CASCADE;
ALTER TABLE test_a_b ADD FOREIGN KEY (b_id) REFERENCES test_b(b_id) ON DELETE CASCADE;
# voeg gebruiker 1 toe
INSERT INTO test_a (a_text) VALUES ('user 1');
# voeg rechten toe
INSERT INTO test_b (b_text) VALUES ('role 1'), ('role 2');
# gebruiker 1 heeft twee rechten
INSERT INTO test_a_b (a_id, b_id) VALUES (1, 1), (1, 2);
# vraag alles op van gebruiker 1
SELECT a.a_id, a.a_text, b.b_id, b.b_text
FROM test_a a
LEFT JOIN test_a_b ab ON (ab.a_id = a.a_id)
LEFT JOIN test_b b ON (b.b_id = ab.b_id)
WHERE a.a_id = 1;
# gebruiker zonder rechten
INSERT INTO test_a (a_text) VALUES ('user 2');
# vraag alles op van gebruiker 2
SELECT a.a_id, a.a_text, b.b_id, b.b_text
FROM test_a a
LEFT JOIN test_a_b ab ON (ab.a_id = a.a_id)
LEFT JOIN test_b b ON (b.b_id = ab.b_id)
WHERE a.a_id = 2;
# alle rollen in 1 rij gescheiden door komma's
SELECT a.*, GROUP_CONCAT(b.b_text) AS rollen
FROM test_a a
LEFT JOIN test_a_b ab ON (ab.a_id = a.a_id)
LEFT JOIN test_b b ON (b.b_id = ab.b_id)
WHERE a.a_id = 2
GROUP BY a.a_id;
CREATE TABLE test_a (
a_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
a_text VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# koppeltabel
CREATE TABLE test_a_b (
a_id INT(10) UNSIGNED NOT NULL,
b_id INT(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# rechten tabel
CREATE TABLE test_b (
b_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
b_text VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# relaties tussen de tabellen, verwijder corresponderende gebruiker of recht als tegenhanger verdwijnt
ALTER TABLE test_a_b ADD FOREIGN KEY (a_id) REFERENCES test_a(a_id) ON DELETE CASCADE;
ALTER TABLE test_a_b ADD FOREIGN KEY (b_id) REFERENCES test_b(b_id) ON DELETE CASCADE;
# voeg gebruiker 1 toe
INSERT INTO test_a (a_text) VALUES ('user 1');
# voeg rechten toe
INSERT INTO test_b (b_text) VALUES ('role 1'), ('role 2');
# gebruiker 1 heeft twee rechten
INSERT INTO test_a_b (a_id, b_id) VALUES (1, 1), (1, 2);
# vraag alles op van gebruiker 1
SELECT a.a_id, a.a_text, b.b_id, b.b_text
FROM test_a a
LEFT JOIN test_a_b ab ON (ab.a_id = a.a_id)
LEFT JOIN test_b b ON (b.b_id = ab.b_id)
WHERE a.a_id = 1;
# gebruiker zonder rechten
INSERT INTO test_a (a_text) VALUES ('user 2');
# vraag alles op van gebruiker 2
SELECT a.a_id, a.a_text, b.b_id, b.b_text
FROM test_a a
LEFT JOIN test_a_b ab ON (ab.a_id = a.a_id)
LEFT JOIN test_b b ON (b.b_id = ab.b_id)
WHERE a.a_id = 2;
# alle rollen in 1 rij gescheiden door komma's
SELECT a.*, GROUP_CONCAT(b.b_text) AS rollen
FROM test_a a
LEFT JOIN test_a_b ab ON (ab.a_id = a.a_id)
LEFT JOIN test_b b ON (b.b_id = ab.b_id)
WHERE a.a_id = 2
GROUP BY a.a_id;
Gewijzigd op 06/02/2019 00:51:32 door Thomas van den Heuvel
Het kan inderdaad voorkomen dat een persoon geen rollen heeft gekoppeld. Dit zou betekenen dat hier actie op gezet moet worden. Dus het is goed dat het inzichtelijk wordt.
ASN is inderdaad de sleutelveld als het gaat om personen. Dit is gegeven en wijzigt verder niet.
Het verschil tussen LEFT en INNER JOIN wordt me nu wel stukken duidelijker. Nooit zo bij stil gestaan. Maar de LEFT variant is idd beter voor gebruik.
Met SELECT voorbeeld is het me gelukt om de koppelingen te leggen en de informatie uit zowel person en role tabel. Als ik het zo zie simpel :-) maar als ik het bedenken moet kom er na 2 avonden niet uit.. TOP MAN. THANKS.
Ik snap niet wat je bedoeld met GROUP_CONCAT hoor... Eens oom Goegel eens vragen.
Moet ik nog wat met die testjes doen dan?
Je hoeft in principe niets te doen met de testjes, maar deze illustreren goed wat er allemaal gebeurt in termen van queryresultaten.