De PHPhulp kroeg
ingewikkelde JOIN query
Ik zit met het volgende probleem:
Ik heb 3 tabellen:
------fietsverkoper--------
id
name
...
...
------fietssoort-------
id
name
...
...
------interactie------
id
soortid
verkoperid
Nu wil ik door een soort fiets in te vullen alle fabrikanten krijgen die die soort verkopen. Dat wil nog prima:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT
fietsverkoper.name
FROM
fietsverkoper
INNER JOIN
interactie
ON
fietsverkoper.id = interactie.verkoperid
WHERE
interactie.soortid = 1
fietsverkoper.name
FROM
fietsverkoper
INNER JOIN
interactie
ON
fietsverkoper.id = interactie.verkoperid
WHERE
interactie.soortid = 1
Maar nu moet het zo dat mensen meerdere fietssoorten kunnen selecteren, en dat alleen de verkopers die alle fietssoorten die zijn geselecteerd uit de query komen.
Ik hoop dat het zo duidelijk is.
Alvast bedankt:D
Gewijzigd op 01/01/1970 01:00:00 door Lasse
Code (php)
1
WHERE interactie.soortid = 1 AND interactie.soortid=2
Ik zal even zeggen hoe de tabelindeling is:
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
----fietsverkoper----
id name
1 piet
2 jan
3 kees
----fietssoort-----
id name
1 merk1
2 merk2
3 merk3
----interactie-----
id soortid verkoperid
1 1 1
2 1 2
3 2 2
4 2 3
5 3 1
6 3 3
id name
1 piet
2 jan
3 kees
----fietssoort-----
id name
1 merk1
2 merk2
3 merk3
----interactie-----
id soortid verkoperid
1 1 1
2 1 2
3 2 2
4 2 3
5 3 1
6 3 3
Ik hoop dat het nu iets duidelijker is:D
Gewijzigd op 01/01/1970 01:00:00 door Lasse
Maar goed, ervan uitgaande dat het op een of andere manier mogelijk moet zijn, ben ik maar even gaan puzzelen.
Het probleem waar je tegen aan loopt, is dat je alleen de records op moet halen waarbij er in de koppeltabel een record bestaat voor alle geselecteerde keuzes. Nu kun je zoals je zelf al terecht opmerkte niet een oplossing gebruiken als:
Code (php)
1
... WHERE soortid = 1 AND soortid = 2
Aangezien soortid natuurlijk nooit tegelijk 2 dezelfde waardes kan hebben. Wel kun je alle records ophalen die aan 1 (of meer) van de geselecteerde waarden voldoen. Om nu te controleren of een bepaalde verkoper ook alle soorten verkoopt (lees: voor elke geselecteerd soort een record in de koppeltabel bestaat), zou je het aantal records per verkoper moeten tellen en dat vervolgens moeten vergelijken met het aantal soorten dat door de gebruiker gekozen is.
De brongegevens waarmee ik aan de slag ben gegaan:
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
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
mysql> SELECT * FROM verkopers;
+----+------+
| id | naam |
+----+------+
| 1 | jan |
| 2 | kees |
| 3 | piet |
+----+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM soort;
+----+-------+
| id | soort |
+----+-------+
| 1 | merk1 |
| 2 | merk2 |
| 3 | merk3 |
+----+-------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM verkoper_soort;
+----+-----+-----+
| id | vid | sid |
+----+-----+-----+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| 6 | 3 | 1 |
| 7 | 3 | 2 |
| 8 | 3 | 3 |
+----+-----+-----+
8 rows in set (0.00 sec)
+----+------+
| id | naam |
+----+------+
| 1 | jan |
| 2 | kees |
| 3 | piet |
+----+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM soort;
+----+-------+
| id | soort |
+----+-------+
| 1 | merk1 |
| 2 | merk2 |
| 3 | merk3 |
+----+-------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM verkoper_soort;
+----+-----+-----+
| id | vid | sid |
+----+-----+-----+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| 6 | 3 | 1 |
| 7 | 3 | 2 |
| 8 | 3 | 3 |
+----+-----+-----+
8 rows in set (0.00 sec)
De variabelen die je nu vooraf moet kennen zijn ten eerste de keuzes die door de gebruiker gemaakt zijn in de vorm van het soortid en het totaal aantal soorten dat door de gebruiker geselecteerd is.
In dit voorbeeld ga ik ervan uit dat een gebruiker op zoek is naar een verkoper die zowel de fietsen van merk1 en merk3 verkoopt. En nu dan de query die dit alles mogelijk maakt:
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
SELECT
verkopers.naam
FROM
verkopers,
(SELECT
vid,
COUNT(*) AS totaal
FROM
verkoper_soort
WHERE
sid IN (1, 3)
GROUP BY
vid
) AS verkoper_soort_result
WHERE
verkoper_soort_result.vid = verkopers.id
AND
verkoper_soort_result.totaal = 2;
verkopers.naam
FROM
verkopers,
(SELECT
vid,
COUNT(*) AS totaal
FROM
verkoper_soort
WHERE
sid IN (1, 3)
GROUP BY
vid
) AS verkoper_soort_result
WHERE
verkoper_soort_result.vid = verkopers.id
AND
verkoper_soort_result.totaal = 2;
Je ziet dat er op 2 plaatsen variabelen ingevuld dienen te worden. Binnen de IN() vul je de geselecteerde soorten gescheiden door een komma in en op de plaats van de 2 komt het totaal aantal geselecteerde soorten te staan.
Om aan te tonen dat dit werkt, ook nog 3 voorbeeldjes:
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
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
mysql> SELECT verkopers.naam FROM verkopers, (SELECT vid, COUNT(*) AS totaal FROM verkoper_soort WHERE sid IN (1,3) GROUP BY vid) AS verkoper_soort_result WHERE verkoper_soort_result.vid = verkopers.id AND verkoper_soort_result.totaal = 2;
+------+
| naam |
+------+
| jan |
| piet |
+------+
2 rows in set (0.00 sec)
mysql> SELECT verkopers.naam FROM verkopers, (SELECT vid, COUNT(*) AS totaal FROM verkoper_soort WHERE sid IN (1,2,3) GROUP BY vid) AS verkoper_soort_result WHERE verkoper_soort_result.vid = verkopers.id AND verkoper_soort_result.totaal = 3;
+------+
| naam |
+------+
| jan |
| piet |
+------+
2 rows in set (0.00 sec)
mysql> SELECT verkopers.naam FROM verkopers, (SELECT vid, COUNT(*) AS totaal FROM verkoper_soort WHERE sid IN (2) GROUP BY vid) AS verkoper_soort_result WHERE verkoper_soort_result.vid = verkopers.id AND verkoper_soort_result.totaal = 1;
+------+
| naam |
+------+
| jan |
| kees |
| piet |
+------+
3 rows in set (0.00 sec)
+------+
| naam |
+------+
| jan |
| piet |
+------+
2 rows in set (0.00 sec)
mysql> SELECT verkopers.naam FROM verkopers, (SELECT vid, COUNT(*) AS totaal FROM verkoper_soort WHERE sid IN (1,2,3) GROUP BY vid) AS verkoper_soort_result WHERE verkoper_soort_result.vid = verkopers.id AND verkoper_soort_result.totaal = 3;
+------+
| naam |
+------+
| jan |
| piet |
+------+
2 rows in set (0.00 sec)
mysql> SELECT verkopers.naam FROM verkopers, (SELECT vid, COUNT(*) AS totaal FROM verkoper_soort WHERE sid IN (2) GROUP BY vid) AS verkoper_soort_result WHERE verkoper_soort_result.vid = verkopers.id AND verkoper_soort_result.totaal = 1;
+------+
| naam |
+------+
| jan |
| kees |
| piet |
+------+
3 rows in set (0.00 sec)
ps. Wie weet is er een minder omslachtige manier. Ik ben benieuwd of die hier dan boven tafel komt.
Gewijzigd op 01/01/1970 01:00:00 door Joren de Wit
Misschien kan het inderdaad nog efficiënter, maar ik vindt het zo ook prima, want ik was al geneigd om met meerdere querys te gaan prutsen.
Persoonlijk had ik denk ik eerder voor zo'n constructie gekozen:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
verkopers.naam
FROM
verkopers
INNER JOIN
(SELECT
vid,
COUNT(*) AS totaal
FROM
verkoper_soort
WHERE
sid IN (1, 3)
GROUP BY
vid
) AS verkoper_soort_result
ON
verkoper_soort_result.vid = verkopers.id
WHERE
verkoper_soort_result.totaal = 2;
verkopers.naam
FROM
verkopers
INNER JOIN
(SELECT
vid,
COUNT(*) AS totaal
FROM
verkoper_soort
WHERE
sid IN (1, 3)
GROUP BY
vid
) AS verkoper_soort_result
ON
verkoper_soort_result.vid = verkopers.id
WHERE
verkoper_soort_result.totaal = 2;
Maar dat is ieder zijn mening:D
In ieder geval hartstikke bedankt!
Jou constructie is hetzelfde als die van Blanche, ware het niet dat jij de tekst INNER JOIN gebruikt. De WHERE verkoper_soort_result.vid=verkopers.id betekend in dit geval een join op die kolommen.
Het verschil in beide query's is dus eigenlijk 0,0 alleen de schrijfwijze is een beetje anders.