Query werkt niet naar behoren
Voor mijn webshop heb ik o.m. twee tabellen:
1. Tabel shop__cd met daarin o.m. de genregegevens van de cd (genre), artikelnummer en het id-nummer (id)
2. Tabel shop__bestellingen met daarin o.m. de besteldatum, artikelnummer
Het genre-veld kan gevuld zijn met één of meerdere genres, zoals geschreven [Gospel][Klassiek]
Nu wil ik de verkoopverhoudingen van de genres in een grafiek zichtbaar maken. Dit doe ik met de volgende query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
case
when c.genre like '%[koor]%' then 'koor'
when c.genre like '%[Gospel]%' then 'gospel'
when c.genre like '%[instrumentaal]%' then 'instrumentaal'
when c.genre like '%[klassiek]%' then 'klassiek'
end as gekocht,
count(b.id) as aantal
FROM
shop__bestellingen as b
JOIN
shop__cd as c
ON
c.artikelnummer = b.artikelnummer
WHERE
MONTH(b.besteldatum) = 7
case
when c.genre like '%[koor]%' then 'koor'
when c.genre like '%[Gospel]%' then 'gospel'
when c.genre like '%[instrumentaal]%' then 'instrumentaal'
when c.genre like '%[klassiek]%' then 'klassiek'
end as gekocht,
count(b.id) as aantal
FROM
shop__bestellingen as b
JOIN
shop__cd as c
ON
c.artikelnummer = b.artikelnummer
WHERE
MONTH(b.besteldatum) = 7
Nu krijg ik als uitkomst alleen het cijfer van de eerste case.
Wat doe ik fout?
George
Toevoeging op 02/08/2013 16:27:38:
En dan krijg je nog niet wat je wilt hebben, case in sql werkt niet hetzelfde als switch, bij een eerste true wordt de rest van de case genegeerd.
Is er een andere oplossing? Ja, normaliseren, meerdere waardes in een kolom opslaan is not done (op enkele uitzonderingen na).
Ik had de GROUP BY inmiddels zelf al gevonden (uit een eerder voorbeeld van jou) en het blijkt nu goed te werken.
Ik heb nu de volgende query:
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
SELECT
case
when c.genre like '%[koor]%' then 'koor'
when c.genre like '%[Gospel]%' then 'gospel'
when c.genre like '%[instrumentaal]%' then 'instrumentaal'
when c.genre like '%[klassiek]%' then 'klassiek'
when c.genre like '%[orgel]%' then 'orgel'
when c.genre like '%[passie]%' then 'passie'
when c.genre like '%[kerst]%' then 'kerst'
end as gekocht,
count(b.id) as aantal
FROM
shop__bestellingen as b
JOIN
shop__cd as c
ON
b.artikelnummer = c.artikelnummer
WHERE
MONTH(besteldatum) = $cActueleMaand
GROUP BY
gekocht
case
when c.genre like '%[koor]%' then 'koor'
when c.genre like '%[Gospel]%' then 'gospel'
when c.genre like '%[instrumentaal]%' then 'instrumentaal'
when c.genre like '%[klassiek]%' then 'klassiek'
when c.genre like '%[orgel]%' then 'orgel'
when c.genre like '%[passie]%' then 'passie'
when c.genre like '%[kerst]%' then 'kerst'
end as gekocht,
count(b.id) as aantal
FROM
shop__bestellingen as b
JOIN
shop__cd as c
ON
b.artikelnummer = c.artikelnummer
WHERE
MONTH(besteldatum) = $cActueleMaand
GROUP BY
gekocht
Als je nu bv een cd hebt met instrumentaal en klassiek als genres, wordt deze niet bij de klassiek meegeteld.
Gewoon twee tabellen erbij, de genres en een koppeltabel cd_genre, dan worden je query's een stuk simpeler.
Welk doel heb jij met de koppeltabel voor ogen?
Toevoeging op 02/08/2013 21:50:28:
Sorry, even nadenken en ik zag/zie de bedoeling en de mogelijkheden. Ook het werk dat dit met zich meebrengt.
Maar goed, voor een goed opgebouwde databaseconstructie moet je wat over hebben.
Buiten in de tuin bij ruim 30 graden kun je nog wel eens op goede gedachten komen.
Stel je hebt twee simpele extra tabellen
genres: genre_id, genre_name
cd_genres: article_id, genre_id
De laatste met een PK op beide kolommen
Dan kan je met deze query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
g.genre_id,
g.genre_name,
COUNT (b.article_id) amount
FROM
genres g
LEFT JOIN
cd_genres
USING (genre_id)
LEFT JOIN
shop__bestellingen b
USING (article_id)
WHERE
b.order_date BETWEEN '2013-07-01' AND '2013-07-31'
GROUP BY
g.genre_id, g.genre_name
g.genre_id,
g.genre_name,
COUNT (b.article_id) amount
FROM
genres g
LEFT JOIN
cd_genres
USING (genre_id)
LEFT JOIN
shop__bestellingen b
USING (article_id)
WHERE
b.order_date BETWEEN '2013-07-01' AND '2013-07-31'
GROUP BY
g.genre_id, g.genre_name
Dat is alleen nog maar de select query, het is ook veel gemakkelijker te beheren
Bedankt voor deze suggestie. Ik ga het zeker gebruiken.
Het gebruik van USING is nieuw voor mij maar ik ga proberen uit te vinden welke mogelijkheden dit heeft.
Weer wat te doen dit weekend
Toevoeging op 03/08/2013 16:46:51:
Ik krijg bij onderstaande code de foutmelding: #1054 - Unknown column 'artikelnummer' in 'from clause'
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
g.g_id,
g.genre,
COUNT (b.artikelnummer) amount
FROM
shop__genre g
LEFT JOIN
kt__genre
USING (g_id)
LEFT JOIN
shop__bestellingen b
USING (artikelnummer)
WHERE
b.besteldatum BETWEEN '2013-07-01' AND '2013-07-31'
GROUP BY
g.g_id, g.genre
g.g_id,
g.genre,
COUNT (b.artikelnummer) amount
FROM
shop__genre g
LEFT JOIN
kt__genre
USING (g_id)
LEFT JOIN
shop__bestellingen b
USING (artikelnummer)
WHERE
b.besteldatum BETWEEN '2013-07-01' AND '2013-07-31'
GROUP BY
g.g_id, g.genre
De tabel met de genres: <strong>shop__genre</strong>, velden genre (genrenaam) en g_id (koppeling met koppeltabel)
De koppeltabel: <strong>kt__genre</strong>, velden g_id (kopp met genretabel) en a_id (kopp met cd-tabel, artikelnummer)
De CD-tabel: <strong>shop__cd</strong>: velden artikelnummer (kopp naar koppeltabel)
Je kan alleen using gebruiken in een join als de kolommen in beide tabellen dezelfde naam hebben, dus:
Code (php)
1
JOIN kt__genre k ON g.g_id = k.g_id
Kan je omzetten naar:
Code (php)
1
JOIN kt__genre USING (g_id)
Anders moet je JOIN ... ON gebruiken
Gewijzigd op 03/08/2013 18:22:48 door Ger van Steenderen