LEFT JOIN met null en waarde

[Verwijderd]

Ik heb 2 tabellen. Tabel 1 is een gebruikers tabel en Tabel 2 is een inlog tabel.

Ik wil een lijst ophalen van mensen die in 2016 zijn ingelogd geweest en niet in 2017.

Ik heb nu echter dit lijst is volgens mij niet juist.
SELECT U.email, L.year FROM users U LEFT JOIN login L ON U.id=L.userId WHERE (L.year = 2016 OR L.year IS NULL)

Hoe zou de query eruit zien vanuit gaand dat Tabel users een email en id kolom heeft en Tabel login userId en year kolom.

Alvast bedankt,
Martijn

[radem205]

SELECT U.email, L.year FROM users U INNER JOIN login L ON U.id=L.userId AND L.year = 2016

De inner join zorgt ervoor dat alleen de users worden getoond waarvan het jaar van inloggen gelijk is aan 2016. Ondanks dat dit mijns inziens beter is, zal jouw query ook gewoon moeten werken. Je geeft namelijk als voorwaarden mee dat jaar gelijk moet zijn aan 2016 of null. De voorwaarde IS NULL moet je wel weglaten om te zorgen dat alleen de records met year = 2016 worden getoond.

[ Voor 84% gewijzigd door radem205 op 21-04-2017 15:51 ]

[Verwijderd]

Hoi radem205,

Dan heb je een lijst van gebruikers die ingelogd waren in 2016 echter de mensen die in 2017 niet zijn ingelogd komen niet in de lijst terect.

[radem205]

Het is wel heel afhankelijk hoe de tabel login is opgebouwd. Wordt voor elke login een aparte record aangemaakt? Of wordt een bestaande record van dezelfde user aangepast?

Mocht je voor elke login een aparte record aanmaken in de database dan kan je ook gebruik maken van onderstaande query. Middels de left join haal je voor elke userid de maximale waarde van de kolom year op. Hiermee weet je dus welke user in welk jaar voor het laatst heeft ingelogd. Middels de WHERE filter je de users die niet ingelogd zijn in 2017 (= YEAR(CURDATE()) ). Als alternatief kan je hier uiteraard voor alleen de mensen die in 2016 zijn ingelogd L.year = 2016 gebruiken. Met onderstaande query zullen alle mensen die ook in 2015 en eerder én de mensen die nog helemaal niet zijn ingelogd worden getoond.

SELECT
U.email,
L.year
FROM
users U
LEFT JOIN
(
SELECT
userId,
MAX(year) AS year
FROM
login
GROUP BY
userId
) AS L
ON
L.userId = U.id
WHERE
L.year != YEAR(CURDATE()) OR L.year IS NULL

[ Voor 76% gewijzigd door radem205 op 21-04-2017 16:14 ]

[GlowMouse]

Die laatste query is onnodig traag en complex. Kijk eens naar WHERE NOT EXISTS.

[radem205]

GlowMouse schreef op vrijdag 21 april 2017 @ 16:15:
Die laatste query is onnodig traag en complex. Kijk eens naar WHERE NOT EXISTS.

Traag is een breed begrip. In het geval dat elke loginsessie wordt gelogd in een nieuwe record, ontkom je er volgens mij niet aan om eerst het meest recente jaar op te vragen. Ik ben dan ook benieuwd hoe je dit met WHERE NOT EXISTS oplost zonder gebruik van een subquery. Immers, een gebruiker die zowel in 2016 als in 2017 is ingelogd, mag niet in het resultaat naar voren komen. Met het gebruik van een subquery middels WHERE NOT EXISTS zal de query wellicht iets sneller worden.

[ Voor 18% gewijzigd door radem205 op 21-04-2017 16:30 ]

[CH4OS]

An sich heb je hiervoor volgens mij niet eens een subquery nodig. Valt me op dat in het forum alhier al gauw geadviseerd wordt ermee, terwijl het niet eens nodig is.

Je kunt alle user ID's toch (gegroepeerd) selecteren uit de login tabel en filteren op datum. Vervolgens join je de user tabel.

@Verwijderd Welke queries heb je zelf zoal geprobeerd? Wat voor resultaten gaven die? Heb je wellicht ook een en ander kunnen vinden met Google?

[ Voor 65% gewijzigd door CH4OS op 21-04-2017 16:33 ]

[radem205]

CH40S schreef op vrijdag 21 april 2017 @ 16:29:
An sich heb je hiervoor volgens mij niet eens een subquery nodig. Valt me op dat in het forum alhier al gauw geadviseerd wordt ermee, terwijl het niet eens nodig is. Je kunt alle user ID's toch selecteren uit de login tabel waar je filtert op datum, vervolgens join je de user tabel en groepeer je de resultset op bijvoorbeeld user id.

Als je inderdaad alleen de users wilt hebben die in 2016 zijn ingelogd is dit inderdaad waar. Mocht je ook de gebruikers willen zien die nog niet zijn ingelogd, waarbij dus geen record in de login tabel bestaat, dan zul je het anders moeten oplossen.

[ Voor 40% gewijzigd door radem205 op 21-04-2017 16:32 ]

[Verwijderd]

Met WHERE MAX(L.year)=2016
(was een reactie op radem205's inmiddels gewijzigde post)

[ Voor 58% gewijzigd door Verwijderd op 21-04-2017 16:36 ]

[CH4OS]

radem205 schreef op vrijdag 21 april 2017 @ 16:30:
Als je inderdaad alleen de users wilt hebben die in 2016 zijn ingelogd is dit inderdaad waar. Mocht je ook de gebruikers willen zien die nog niet zijn ingelogd, waarbij dus geen record in de login tabel bestaat, dan zul je het anders moeten oplossen.

Maar de TS wil een lijst met users die in 2016 waren ingelogt:

Ik wil een lijst ophalen van mensen die in 2016 zijn ingelogd geweest en niet in 2017.

Verwijderd schreef op vrijdag 21 april 2017 @ 16:33:
Met WHERE MAX(L.year)=2016
(was een reactie op CH40S' inmiddels gewijzigde post)

Dit kan alleen als kolom 'year' van het type integer is, volgens mij. Mocht de kolom year een datetime of wat dan ook met datum zijn, dan gaat dit niet meer, volgens mij.

[ Voor 25% gewijzigd door CH4OS op 21-04-2017 16:36 ]

[radem205]

CH40S schreef op vrijdag 21 april 2017 @ 16:35:
[...]
Maar de TS wil een lijst met users die in 2016 waren ingelogt:
[...]

Klopt, alleen uit z'n query maak ik op dat hij ook de users wil hebben die nog niet zijn ingelogd. Dus wellicht kan de TS af met een query zonder subquery.

[Verwijderd]

CH40S schreef op vrijdag 21 april 2017 @ 16:35:
Dit kan alleen als kolom 'year' van het type integer is, volgens mij. Mocht de kolom year een datetime of wat dan ook met datum zijn, dan gaat dit niet meer, volgens mij.

Hij gebruikt zelf L.year, maar anders kun je gewoon MAX(YEAR(L.datum)) doen.

[CH4OS]

Verwijderd schreef op vrijdag 21 april 2017 @ 16:38:
Hij gebruikt zelf L.year, maar anders kun je gewoon YEAR(L.datum) doen.

Weet ik, maar wilde niet het antwoord direct geven.

[Verwijderd]

Maar het was geen antwoord, want geen datumveld...

[CH4OS]

Omdat (imo) de TS onduidelijk is in hoe het database ontwerp eruit ziet.

[GlowMouse]

radem205 schreef op vrijdag 21 april 2017 @ 16:19:
[...]

Traag is een breed begrip. In het geval dat elke loginsessie wordt gelogd in een nieuwe record, ontkom je er volgens mij niet aan om eerst het meest recente jaar op te vragen

Ik ben benieuwd naar het execution plan van jouw query, maar bij WHERE NOT EXISTS weet ik zeker dat je hem met indices kunt versnellen.

[DJMaze]

Je mist de HAVING

SQL:

SELECT U.email, MAX(L.year) max_year
FROM users U
LEFT JOIN login L ON U.id=L.userId AND L.year >= 2016
GROUP BY 1
HAVING max_year = 2016

En als je dan ook de mensen wil hebben die nooit hebben ingelogd in 2016 en 2017:

SQL:

SELECT U.email, MAX(L.year) max_year
FROM users U
LEFT JOIN login L ON U.id=L.userId AND L.year >= 2016
GROUP BY 1
HAVING max_year = 2016 OR max_year IS NULL

radem205 schreef op vrijdag 21 april 2017 @ 16:30:
Mocht je ook de gebruikers willen zien die nog niet zijn ingelogd, waarbij dus geen record in de login tabel bestaat, dan zul je het anders moeten oplossen.

Ja en nee, hangt er vanaf of zijn RDBMS de HAVING ondersteund

[ Voor 81% gewijzigd door DJMaze op 22-04-2017 04:34 ]

[Verwijderd]

Bedankt allemaal voor jullie reacties. Ik heb het ondertussen al opgelost en ik heb het zo gedaan

SELECT L.userId, U.email, SUM( IF(L.year=2016 THEN 1 ELSE 0) ) AS LoginCount2016, SUM( IF(L.year=2017 THEN 1 ELSE 0) ) AS LoginCount2017 FROM Login L INNER JOIN users U ON U.id=L.userId GROUP BY L.userId, U.email

Hiermee heb ik een overzicht hoevaak iemand heeft ingelogd. Eventueel kan ik ook nog een where doen aan de hand van LoginCount2016 en/of LoginCount2017

[DJMaze]

Leer eens code=sql tag te gebruiken zoals:

SQL:

SELECT
    L.userId,
    U.email,
    SUM( IF(L.year=2016 THEN 1 ELSE 0) ) AS LoginCount2016,
    SUM( IF(L.year=2017 THEN 1 ELSE 0) ) AS LoginCount2017
FROM Login L
INNER JOIN users U ON U.id=L.userId
GROUP BY L.userId, U.email

Verwijderd schreef op dinsdag 25 april 2017 @ 11:52:
Hiermee heb ik een overzicht hoevaak iemand heeft ingelogd. Eventueel kan ik ook nog een where doen aan de hand van LoginCount2016 en/of LoginCount2017

Ik wens je veel succes met je "where"