[SQL] Tabel join welke bij geen match: standaard resultaat

[The-Source]

Mijn vraag
Ik heb een database gemaakt (kan nog aangepast worden indien nodig) welke bestaat uit 2 tabellen:
Tabel 1: locaties
klantnummer (PK, int, not null)
locatie (PK, varchar(32), not null)
gebouw (varchar(32), null)

Tabel 2: log
index (PK, int, not null)
klantnummer (int, not null)
locatie (varchar(32), not null)
oproeppunt (varchar(32), not null)
datumtijd(datetime, not null)
status (varchar(5), not null)

In tabel locaties komen alle locaties te staan welke via script uit een backup file genereert worden. In theorie zal elke locatie ook een gebouw moeten hebben maar het kan in een exotisch geval voorkomen dat dit niet het geval is (nog niet tegen gekomen)

In tabel log komen de diverse 'set' en 'clear' meldingen van de diverse locaties. (1 locatie kan meermaals in log voorkomen)
De reden van toevoeging oproeppunt is dat 1 locaties diverse oproeppunten kan hebben. Deze is voor mijn overzicht nu niet belangrijk
Zoals in het voorbeeld te zien is, kunnen set en clear door elkaar heen komen.

code:

index   klantnummer locatie oproeppunt  datumtijd                   status
3250    111222      B344P.  B344P       2020-02-29 23:10:33.000     Set
3251    111222      D185P.  D185P       2020-02-29 23:10:35.000     Clear

Log.locatie en locaties.locatie zijn altijd identiek qua tekst.

Wat wil ik gaan bereiken:
Ik wil met de query alle meeste recente log vermelding hebben van alle locaties. Als deze niet in log voorkomen zou ik het liefst een standaard waarde bij het resultaat willen zien. Het mooiste resultaat zou voor mij als volgt zijn:

code:

klantnummer locatie gebouw  datumtijd
111222      D100s   Geb.A   2020-03-17 12:30:00
111222      D101p   Geb.A   Geen oproep
111222      D105s   Geb.A   2020-03-17 12:40:00

Relevante software en hardware die ik gebruik
MS SQL Server express 15.0
SQL studio management 18.4

Query wordt uiteindelijk via powershell gebruikt maar het vullen van de database doe ik daar nu ook al mee dus als de juiste query er is zal dat geen probleem moeten vormen.

Wat ik al gevonden of geprobeerd heb

SQL:

SELECT log.locatie, max(log.datumtijd) As tijdstip from log left join locaties ON log.locatie=locaties.locatie where status='set' group by log.locatie order by log.locatie

resultaat:

code:

locatie     oproep
 D195P-1    2020-03-10 12:13:25.000
 D195P-2    2020-03-10 11:47:34.000

De spatie voor D195 is onderdeel van de naam
Dit geeft resultaat maar zonder de gewenste extra kolommen. Als ik locaties.klantnummer en locaties.gebouw in de select toevoeg komt er een 'Column 'locaties.klantnummer' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

Ook met de volgende query krijg ik een error:

SQL:

SELECT locaties.locatie, max(log.datumtijd) As Tijdstip from log locaties full outer join locaties ON log.locatie=locaties.locatie where status='set' group by log.locatie order by log.locatie

error:

Msg 1012, Level 16, State 1, Line 1
The correlation name 'locaties' has the same exposed name as table 'locaties'.

Heb ook nog wat geprobeerd met COALESCE maar ook hier kom ik niet verder mee

[Woy]

Je zult altijd moeten groeperen op alle velden die je niet aggregeert zoals je bijvoorbeeld met je MAX aanroep doet. Je zou dus kunnen kijken of je je GROUP BY statement uit kunt breiden, of je kunt kijken naar een "Groupwise Maximum".

Daarna kun je COALESCE inderdaad gebruiken om de NULL values te vervangen voor je defaults.

[ Voor 124% gewijzigd door Woy op 17-03-2020 13:32 ]

[The-Source]

Ik krijg met je tip in ieder geval meer kolommen zichtbaar, query is nu als volgt:

SQL:

SELECT locaties.locatie, locaties.gebouw, COALESCE(max(log.datumtijd), 'geen oproep') As tijdstip from log left join locaties ON log.locatie=locaties.locatie where status='set' group by locaties.gebouw, locaties.locatie order by locaties.locatie

Dit geeft 1394 regels als resultaat en overal staat er werkelijk een tijdstip vermeld.
In totaal zijn er 2150 locaties en dat betekend dat ik de locaties zonder log vermelding nu nog niet ziet.
Het is wel zo dat de 1e regel locatie NULL en Gebouw NULL staat. Dit kan misschien wel liggen aan de data in de log tabel. Maar misschien ook wel weer handig om te weten.

Met een kleine aanpassing in de JOIN komen deze NULL waardes niet meer voor:

SQL:

SELECT locaties.locatie, locaties.gebouw, COALESCE(max(log.datumtijd), 'geen oproep') As tijdstip from locaties left join log ON locaties.locatie=log.locatie where log.status='set' and log.pnummer= 150681 group by locaties.gebouw, locaties.locatie order by locaties.locatie

Maar nog steeds geen lege regels...

[ Voor 35% gewijzigd door The-Source op 17-03-2020 14:41 . Reden: toevoeging ]

[Woy]

The-Source schreef op dinsdag 17 maart 2020 @ 14:24:
Ik krijg met je tip in ieder geval meer kolommen zichtbaar, query is nu als volgt:

SQL:

SELECT locaties.locatie, locaties.gebouw, COALESCE(max(log.datumtijd), 'geen oproep') As tijdstip from log left join locaties ON log.locatie=locaties.locatie where status='set' group by locaties.gebouw, locaties.locatie order by locaties.locatie

In totaal zijn er 2150 locaties en dat betekend dat ik de locaties zonder log vermelding nu nog niet ziet.

In je where clause geef je ook aan dat je dat niet wil

SQL:

where log.status='set'

[The-Source]

Dacht zelf dat de status filter nodig was maar door de MAX maakt het niet heel veel uit dat ik specifiek set ipv clear pak.

SQL:

SELECT locaties.klantnummer, locaties.locatie, locaties.gebouw, COALESCE(max(log.datumtijd), '1999-01-01 01:01:01.000') As tijdstip from locaties left join log ON locaties.locatie=log.locatie where locaties.klantnummer=150681 group by locaties.klantnummer, locaties.gebouw, locaties.locatie order by locaties.locatie

Kwam er achter dat de COALESCE vervangende waarde ook als datum opgegeven moest worden. Niet wenselijk maar wel omheen te werken aangezien alle log vermeldingen toch niet ouder zijn dan 2 jaar.

[CurlyMo]

Dus het werkt nu?

Tip voor de volgende keer. SQL is net als ieder andere taal leesbaarder met een beetje opmaak. Bijv.

SQL:

select
   locaties.klantnummer,
   locaties.locatie,
   locaties.gebouw,
   coalesce(max(log.datumtijd), '1999-01-01 01:01:01.000') as tijdstip
from
   locaties
left join
   log
on
   locaties.locatie = log.locatie
where
   locaties.klantnummer = 150681
group by
   locaties.klantnummer, locaties.gebouw, locaties.locatie
order by
   locaties.locatie

Gelijk een beetje consequent hoofdletter / kleine letter gebruik bij statements.
Ook fijner voor je mede tweakers

[The-Source]

Ja het werkt met de query uit mijn vorige post.
Hoop hem morgen weer verder te kunnen uitwerken en ook in mijn powershell script te kunnen toepassen. Aangezien insert & delete al prima werken verwacht ik daarbij niet te veel uitdagingen

[doskabouter]

Als je de max(log.datumtijd) kan casten naar een text kan je het coalescen met iedere gewenste text

[DataGhost]

Of je houdt lekker gewoon de NULL (dus geen gedoe met coalescen met magic values, en zeker niet casten naar text) en lost de verwerking daarvan verder in je applicatie op.

[The-Source]

Dat casten naar NULL had ik even niet aan gedacht, net even getest en werkt ook prima. Scheelt weer om dat later in de applicatie te doen.

[DataGhost]

Casten naar NULL? Als je een left/right join doet worden de velden die in een bepaalde rij niet bestaan zelf al NULL, daar hoef je verder niks voor te doen.

Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null.

Mag je raden waarom COALESCE(max(log.datumtijd), '1999-01-01 01:01:01.000') ooit '1999-01-01 01:01:01.000' zou teruggeven

[ Voor 9% gewijzigd door DataGhost op 19-03-2020 11:04 ]