Vraagje over join met 2 tabellen
Ik loop even vast bij het koppelen van gegevens uit twee tabellen. Het is waarschijnlijk ontzettend simpel, maar ik wordt een beetje duizelig van alle mogelijkheden met join. Ik heb de volgende twee tabellen:
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
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
<?php
// Een klanten tabel
CREATE TABLE IF NOT EXISTS `klanten` (
`klant_id` int(10) unsigned NOT NULL auto_increment,
`geslacht` char(1) NOT NULL default '',
`voorletters` varchar(40) NOT NULL default '',
`tussenvoegsel` varchar(15) default NULL,
`achternaam` varchar(40) NOT NULL default '',
`straatnaam` varchar(22) NOT NULL default '',
`huisnr` varchar(8) NOT NULL default '',
`pc_cijfers` varchar(4) NOT NULL default '',
`pc_letters` char(2) NOT NULL default '',
`woonplaats` varchar(35) NOT NULL default '',
`telefoon` varchar(15) default NULL,
`email` varchar(40) NOT NULL default '',
`afname` char(1) NOT NULL default '',
`active` varchar(32) default NULL,
`datum` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`klant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=73 ;
// Een tabel waar het geslacht in staat
CREATE TABLE IF NOT EXISTS `klanten_geslacht` (
`kg_id` bigint(20) NOT NULL auto_increment,
`geslacht` varchar(255) NOT NULL,
PRIMARY KEY (`kg_id`)
);
?>
// Een klanten tabel
CREATE TABLE IF NOT EXISTS `klanten` (
`klant_id` int(10) unsigned NOT NULL auto_increment,
`geslacht` char(1) NOT NULL default '',
`voorletters` varchar(40) NOT NULL default '',
`tussenvoegsel` varchar(15) default NULL,
`achternaam` varchar(40) NOT NULL default '',
`straatnaam` varchar(22) NOT NULL default '',
`huisnr` varchar(8) NOT NULL default '',
`pc_cijfers` varchar(4) NOT NULL default '',
`pc_letters` char(2) NOT NULL default '',
`woonplaats` varchar(35) NOT NULL default '',
`telefoon` varchar(15) default NULL,
`email` varchar(40) NOT NULL default '',
`afname` char(1) NOT NULL default '',
`active` varchar(32) default NULL,
`datum` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`klant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=73 ;
// Een tabel waar het geslacht in staat
CREATE TABLE IF NOT EXISTS `klanten_geslacht` (
`kg_id` bigint(20) NOT NULL auto_increment,
`geslacht` varchar(255) NOT NULL,
PRIMARY KEY (`kg_id`)
);
?>
Bij geslacht in de eerste tabel staat een 1 voor Dhr. en een 2 voor Mevr.
Nu wil ik graag een lijst uitdraaien van alle klanten, waarbij de 1 en 2 bij geslacht netjes vervangen worden door ofwel Dhr. ofwel Mevr.
Iemand een goede suggestie voor een query hiervoor??
De afname moet bijvoorbeeld ook gekoppeld worden aan een nieuwe tabel.
En ondertussen staat de database al aardig gevuld..
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
// Dit is de tabel voor de afname
CREATE TABLE IF NOT EXISTS `klanten_afname` (
`ka_id` bigint(20) NOT NULL auto_increment,
`afname` varchar(255) NOT NULL,
PRIMARY KEY (`ka_id`)
);
INSERT INTO `klanten_afname` VALUES (1, '1 stapel');
INSERT INTO `klanten_afname` VALUES (2, '2 stapels');
INSERT INTO `klanten_afname` VALUES (3, '3 stapels');
INSERT INTO `klanten_afname` VALUES (4, 'Meer stapels, neem contact met mij op!');
?>
// Dit is de tabel voor de afname
CREATE TABLE IF NOT EXISTS `klanten_afname` (
`ka_id` bigint(20) NOT NULL auto_increment,
`afname` varchar(255) NOT NULL,
PRIMARY KEY (`ka_id`)
);
INSERT INTO `klanten_afname` VALUES (1, '1 stapel');
INSERT INTO `klanten_afname` VALUES (2, '2 stapels');
INSERT INTO `klanten_afname` VALUES (3, '3 stapels');
INSERT INTO `klanten_afname` VALUES (4, 'Meer stapels, neem contact met mij op!');
?>
Gewijzigd op 01/01/1970 01:00:00 door Dennis Jongerden
Normaliseren is vooral voor het voorkomen van dubbele gegevens, wat dat betreft kan ik me jou keuze wel voorstellen, maar wat ik het belangrijkste voordeel vind van normaliseren is dat je zonder "grote" veranderingen ook items kan toevoegen.
Zoiets zou het moeten worden denk ik in jou geval (niet getest, zo uit het hoofd):
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT
klanten.naam,
kg.geslacht
FROM
klanten
INNER JOIN
klanten_geslacht AS kg
ON
kg.geslacht=klanten.geslacht
klanten.naam,
kg.geslacht
FROM
klanten
INNER JOIN
klanten_geslacht AS kg
ON
kg.geslacht=klanten.geslacht
En zoiets wanneer je bijvoorbeeld 1 als man en 2 als vrouw wilt, en je de hele tabel met de namen van geslachten weg zou halen: (wederom niet getest, zo uit het hoofd!)
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT
naam,
CASE geslacht
WHEN 1 THEN 'Dhr.'
WHEN 2 THEN 'Mevr.'
END CASE
AS aanhef
FROM tabel;
naam,
CASE geslacht
WHEN 1 THEN 'Dhr.'
WHEN 2 THEN 'Mevr.'
END CASE
AS aanhef
FROM tabel;
Edit:
Verder merk ik dat je opzet niet helemaal lekker is uitgewerkt, Wat nou als ik als klant 2 keer een bestelling plaats? Het id in je klanten_afname tabel lijkt mij overeen te moeten komen met het klantenid, maar dit kan je beter zo oplossen:
klanten_afnames:
- id
- klantid
- afname
- datum (om ook bij te houden of het om oude of nieuwe afnames gaat. Kan je een mooi overzicht ook nog maken elk jaar)
Als je aan zoiets gaat beginnen, bedenk dan eerst goed wat wel en niet mogelijk moet zijn, en bepaal zo precies mogelijk wat voor gegevens er in de tabellen moeten komen. Ik heb het gevoel dat je meer "gewoon" bent begonnen.
Verder merk ik dat je opzet niet helemaal lekker is uitgewerkt, Wat nou als ik als klant 2 keer een bestelling plaats? Het id in je klanten_afname tabel lijkt mij overeen te moeten komen met het klantenid, maar dit kan je beter zo oplossen:
klanten_afnames:
- id
- klantid
- afname
- datum (om ook bij te houden of het om oude of nieuwe afnames gaat. Kan je een mooi overzicht ook nog maken elk jaar)
Als je aan zoiets gaat beginnen, bedenk dan eerst goed wat wel en niet mogelijk moet zijn, en bepaal zo precies mogelijk wat voor gegevens er in de tabellen moeten komen. Ik heb het gevoel dat je meer "gewoon" bent begonnen.
Gewijzigd op 01/01/1970 01:00:00 door Robert Deiman
Bedankt dat je met me meedenkt. Ik ben enigszins gewoon begonnen, maar een klant schrijft zich maar 1 keer in en geeft hierbij aan hoeveel hij wil afnemen. Dus er komen geen herhaalaankopen ofzo. het is puur bedoeld als inschrijvingsdatabase.
Ik ben even jouw suggesties aan het testen. Zelf kwam ik overigens, naar aanleiding van jouw suggestie met CASE op de volgende query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
$sql = "SELECT
klant_id,
ELT(geslacht,
'Dhr.',
'Mevr.')
AS geslacht,
voorletters,
tussenvoegsel,
achternaam,
straatnaam,
huisnr,
pc_cijfers,
pc_letters,
woonplaats,
afname
FROM klanten";
?>
$sql = "SELECT
klant_id,
ELT(geslacht,
'Dhr.',
'Mevr.')
AS geslacht,
voorletters,
tussenvoegsel,
achternaam,
straatnaam,
huisnr,
pc_cijfers,
pc_letters,
woonplaats,
afname
FROM klanten";
?>
Gewijzigd op 01/01/1970 01:00:00 door Dennis Jongerden
Bovenstaande query met ELT() werkt op zich prima. Dan heb ik geen meerdere tabellen nodig. Maar toch wil ik graag eens kijken of er toch een query mogelijk is met een JOIN(). Maar ik krijg het gewenste resultaat er niet uit.
Dit is wat ik nu heb:
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
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
<?php
$sql = "SELECT
k.klant_id,
kg.geslacht AS geslacht,
k.voorletters,
k.tussenvoegsel,
k.achternaam,
k.straatnaam,
k.huisnr,
k.pc_cijfers,
k.pc_letters,
k.woonplaats
FROM klanten AS k
LEFT JOIN klanten_geslacht AS kg ON kg.geslacht=k.geslacht";
// Fetch and display the results
$result=$db->query($sql);
$content .= "<table class=\"datagrid\" cellspacing=\"0\">\n";
$content .= "<tr><th>Id</th><th>Naam</th><th>Adres</th><th>Postcode</th><th>Plaats</th><th>Datum</th></tr>\n";
while ( $row=$result->fetch() )
{
$content .= "<tr>\n";
$content .= "<td>".$row['klant_id']."</td>\n";
$content .= "<td>".$row['geslacht']." ".$row['voorletters']." ".$row['tussenvoegsel']." ".$row['achternaam']."</td>\n";
$content .= "<td>".$row['straatnaam']." ".$row['huisnr']."</td>\n";
$content .= "<td>".$row['pc_cijfers']." ".$row['pc_letters']."</td>\n";
$content .= "<td>".$row['woonplaats']."</td>\n";
$content .= "<td></td>\n";
$content .= "</tr>\n";
}
$content .= "</table>\n";
?>
$sql = "SELECT
k.klant_id,
kg.geslacht AS geslacht,
k.voorletters,
k.tussenvoegsel,
k.achternaam,
k.straatnaam,
k.huisnr,
k.pc_cijfers,
k.pc_letters,
k.woonplaats
FROM klanten AS k
LEFT JOIN klanten_geslacht AS kg ON kg.geslacht=k.geslacht";
// Fetch and display the results
$result=$db->query($sql);
$content .= "<table class=\"datagrid\" cellspacing=\"0\">\n";
$content .= "<tr><th>Id</th><th>Naam</th><th>Adres</th><th>Postcode</th><th>Plaats</th><th>Datum</th></tr>\n";
while ( $row=$result->fetch() )
{
$content .= "<tr>\n";
$content .= "<td>".$row['klant_id']."</td>\n";
$content .= "<td>".$row['geslacht']." ".$row['voorletters']." ".$row['tussenvoegsel']." ".$row['achternaam']."</td>\n";
$content .= "<td>".$row['straatnaam']." ".$row['huisnr']."</td>\n";
$content .= "<td>".$row['pc_cijfers']." ".$row['pc_letters']."</td>\n";
$content .= "<td>".$row['woonplaats']."</td>\n";
$content .= "<td></td>\n";
$content .= "</tr>\n";
}
$content .= "</table>\n";
?>
Alles gaat goed, alleen geeft ie geen resultaat voor geslacht. Overigens ook geen foutmelding ofzo, maar geslacht blijft gewoon leeg..