[MySQL] aantal kliks uitlezen

[Maarten Wierda]

Hallo,

Ik heb een vraagje over het opstellen van een querie. Voor een
applicatie wil ik per verzonden mailing alle links uit deze mailing
opvragen en het aantal keer dat er op een link is geklikt.

Onderstaande querie haalt wel alle links op waar op geklikt is, maar
ik wil ook de links opvragen waar _niet_ op is geklikt.

SELECT
url,
COUNT(*) AS aantal_geklikt
FROM
linktrack_geklikt AS k,
linktrack AS l
WHERE
k.linkID = l.linkID
AND
k.mailingID = '7316'
GROUP BY
l.linkID

Er zijn twee tabellen aanwezig. Dit is de structuur.

linktrack
---------
linkID
mailingID
url

linktrack_geklikt
-----------------
klikID
linkID
mailingID
userID

In de tabel 'linktrack' staan alle links per mailing, elke URL is
voorzien van een 'linkid'. De tabel 'linktrack_geklikt' bevat de
gegevens van elke 'klik', de kolom 'mailingID' is denk ik overbodig,
maar is toch voor het /gemak/ aanwezig.

De querie die ik gaf geeft dus niet het gewenste resultaat. Ik heb het
vermoeden dat ik met een zog. LEFT JOIN moet werken om i.i.g. alle
resultaten (alle links per mailing) te selecteren, maar ik kom er niet
helemaal uit.

Gr. Maarten Wierda

[Johan Schoone]

Maarten Wierda wrote:

> Hallo,
>
> Ik heb een vraagje over het opstellen van een querie. Voor een
> applicatie wil ik per verzonden mailing alle links uit deze mailing
> opvragen en het aantal keer dat er op een link is geklikt.

[Beschrijving van query + tabellen, ook de links waarop *niet* is geklikt
moeten in de lijst voorkomen.]

Probeer het eens met een UNION:

SELECT url, COUNT(*) AS aantal_geklikt
FROM linktrack_geklikt AS k, linktrack AS l
WHERE k.linkID = l.linkID AND k.mailingID = '7316'
GROUP BY l.linkID
UNION
SELECT url, 0 AS aantal_geklikt
FROM linktrack AS l
WHERE NOT EXISTS (
SELECT linkID FROM linktrack_geklikt AS k
WHERE k.linkID = l.linkID
)
--
Assume nothing, expect anything.

[Ronald Paul]

maarten@optionalt.nl (Maarten Wierda) schreef:

>Ik heb een vraagje over het opstellen van een querie. Voor een
>applicatie wil ik per verzonden mailing alle links uit deze mailing
>opvragen en het aantal keer dat er op een link is geklikt.
>
>Onderstaande querie haalt wel alle links op waar op geklikt is, maar
>ik wil ook de links opvragen waar _niet_ op is geklikt.

[..]

>Er zijn twee tabellen aanwezig. Dit is de structuur.
>
>linktrack
>---------
>linkID
>mailingID
>url
>
>linktrack_geklikt
>-----------------
>klikID
>linkID
>mailingID
>userID
>
>In de tabel 'linktrack' staan alle links per mailing, elke URL is
>voorzien van een 'linkid'. De tabel 'linktrack_geklikt' bevat de
>gegevens van elke 'klik', de kolom 'mailingID' is denk ik overbodig,
>maar is toch voor het /gemak/ aanwezig.
>
>De querie die ik gaf geeft dus niet het gewenste resultaat. Ik heb het
>vermoeden dat ik met een zog. LEFT JOIN moet werken om i.i.g. alle
>resultaten (alle links per mailing) te selecteren, maar ik kom er niet
>helemaal uit.

Je moet het inderdaad zoeken bij een LEFT JOIN. Mijn poging:

SELECT url, COUNT(lk.klikID) AS aantal_geklikt
FROM linktrack l
LEFT JOIN linktrack_geklikt lk ON l.linkID=lk.linkID
WHERE k.mailingID = '7316'
GROUP BY l.linkID

--
Groet, Ronald

[Maarten Wierda]

Ronald Paul wrote:

> Je moet het inderdaad zoeken bij een LEFT JOIN. Mijn poging:
>
> SELECT url, COUNT(lk.klikID) AS aantal_geklikt
> FROM linktrack l
> LEFT JOIN linktrack_geklikt lk ON l.linkID=lk.linkID
> WHERE k.mailingID = '7316'
> GROUP BY l.linkID

Dit levert helaas hetzelfde resultaat als de LEFT JOIN die ik eerder had
geprobeerd. Alleen de links waar op is geklikt worden geretourneerd,
niet _alle_ links

In ieder geval bedankt.


--
Cheers,
Maarten Wierda

[Ronald Paul]

Maarten Wierda <maartenAT@norecessDOT.com> schreef:

>> Je moet het inderdaad zoeken bij een LEFT JOIN. Mijn poging:
>>
>> SELECT url, COUNT(lk.klikID) AS aantal_geklikt
>> FROM linktrack l
>> LEFT JOIN linktrack_geklikt lk ON l.linkID=lk.linkID
>> WHERE k.mailingID = '7316'
>> GROUP BY l.linkID
>
>Dit levert helaas hetzelfde resultaat als de LEFT JOIN die ik eerder had
>geprobeerd. Alleen de links waar op is geklikt worden geretourneerd,
>niet _alle_ links

Wat heb je van k.mailingID gemaakt dan? Want de alias k bestaat niet
in dit voorbeeld (foutje) :-) Ik heb het hier zelf nog even getest, en
het komt er op neer dat als je hier l.mailingID van maakt, het wel
moet werken. Mijn SQL-code voor mijn eigen test:

CREATE TABLE link
(
id tinyint(4) NOT NULL,
mailingId tinyint(4) NOT NULL
);

CREATE TABLE link_klik
(
id tinyint(4) NOT NULL,
linkId tinyint(4) NOT NULL
);

INSERT INTO link (id, mailingId) VALUES(1, 1);
INSERT INTO link (id, mailingId) VALUES(2, 1);
INSERT INTO link (id, mailingId) VALUES(3, 2);
INSERT INTO link (id, mailingId) VALUES(4, 1);
INSERT INTO link_klik (id, linkId) VALUES(1, 1);
INSERT INTO link_klik (id, linkId) VALUES(2, 1);
INSERT INTO link_klik (id, linkId) VALUES(3, 1);
INSERT INTO link_klik (id, linkId) VALUES(4, 3);
INSERT INTO link_klik (id, linkId) VALUES(5, 4);
INSERT INTO link_klik (id, linkId) VALUES(6, 4);

SELECT *, COUNT(lk.id) AS aantalKliks
FROM link l
LEFT JOIN link_klik lk ON l.id=lk.linkId
WHERE l.mailingId=1
GROUP BY l.id;

Geeft dit als resultaat:
+----+-----------+------+--------+-------------+
| id | mailingId | id | linkId | aantalKliks |
+----+-----------+------+--------+-------------+
| 1 | 1 | 1 | 1 | 3 |
| 2 | 1 | NULL | NULL | 0 |
| 4 | 1 | 5 | 4 | 2 |
+----+-----------+------+--------+-------------+
3 rows in set (0.01 sec)

--
Groet, Ronald

[Maarten Wierda]

Ronald Paul wrote:

> Wat heb je van k.mailingID gemaakt dan? Want de alias k bestaat niet
> in dit voorbeeld (foutje) :-)

Inderdaad, stom stom stom. In alle haast overheen gelezen.

[snip SQL-code & test]

Hartstikke bedankt voor je moeite, nu werkt het prima.


--
Cheers,
Maarten Wierda