[SQL] Verdubbeling van rows met JOIN

[the-edge]

Mijn vraag:
Het is een bekend issue, maar ondanks dat, krijg ik het maar niet opgelost.
Concreet is het volgende het geval:

Ik heb 3 tabellen:
- Trip
- TripActiviteit
- Message

Trip bevat ritten, uniek gemaakt door een TripNr.
Tripactiviteit bevat activiteiten die hangen aan Trip
Message bevat status berichten met bijvoorbeeld een starttijd ook daar is het Trip value van toepassing

Wanneer ik los de volgende queries uitvoer, op basis van 1 rit krijg ik een net resultaat:

SQL:

SELECT * FROM Trip WHERE TripID = 12345
SELECT * FROM TripActiviteit WHERE Trip = 12345
SELECT * FROM Message WHERE Trip = 12345

So far, so good.
Nu wil ik het eea destilleren en verduidelijken, maar daar kom ik niet uit. Doel is dat ik per 'TripActiviteit' uit de tabel 'Message' de value van de kolom 'StartTime' wil hebben.

Dit dacht ik op de volgende manier op te lossen:

SQL:

SELECT
Tr.TripNr,
msg.Starttime

FROM Trip tr
LEFT JOIN TripActiviteit as act ON tr.Tripnr = act.Trip
LEFT JOIN Message as msg ON act.Trip = msg.trip

WHERE
tr.TripNr = 12345

Echter, ik krijg nu 4 resultaten terug. 2 x 2 in feite. Ze zijn exact hetzelfde.


Wat ik al gevonden of geprobeerd heb
- Distinct geprobeerd na SELECT - werkt niet
- Group BY geprobeerd. Maar er zitten zoveel kolommen in dat ik tegen "Column name xx is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
- Verschillende joins geprobeerd, in de hoop dat ik een fout maakte.

Ik draai in, waarschijnlijk, een beginnersfout rondje. Maar ik kom er niet uit, wie kan me de goede richting in helpen?

[The Eagle]

Beginnersfoutje idd. Je hebt in een van de tabellen een extra sleutelwaarde die de rij uniek maakt. Die selecteer je nu niet, dus dan krijg je dubbelingen omdat beide rijen aan het zelfde selectiecriterium voldoen.
Ergo: ergens moet je joinen op een veld meer, wellicht zelfs op meerdere velden.

[chengbondkwok]

Als je alleen joined op TripActiviteit, krijg je dan ook dubbele waarden uit?

[DataGhost]

Distinct zou wel moeten werken als je je query in een subquery gooit. Of dat mooi is, is een tweede, want het zou zomaar zo kunnen zijn dat je een enorme dataset aan het maken bent met je ("foute") join, terwijl betere joincondities je direct al het goeie terug kunnen geven. In principe zijn de rijen uit je query niet gelijk, maar selecteer je kennelijk alleen maar op velden waar je dat niet bij ziet dus lijkt het alsof ze dat wel zijn. Je zal doorgaans toch moeten groeperen/aggregeren, of in je where wat dingen uitsluiten, als je je JOIN niet aanpast.

[the-edge]

chengbondkwok schreef op vrijdag 20 december 2019 @ 14:26:
Als je alleen joined op TripActiviteit, krijg je dan ook dubbele waarden uit?

Nee, dan krijg ik netjes 2 (unieke) waarden.

The Eagle schreef op vrijdag 20 december 2019 @ 14:26:
Beginnersfoutje idd. Je hebt in een van de tabellen een extra sleutelwaarde die de rij uniek maakt. Die selecteer je nu niet, dus dan krijg je dubbelingen omdat beide rijen aan het zelfde selectiecriterium voldoen.
Ergo: ergens moet je joinen op een veld meer, wellicht zelfs op meerdere velden.

Met de constatering van wat @chengbondkwok me geeft zou er dus een extra sleutelwaarde in de Message tabel moeten zitten. Kan ik dat op 1 of andere manier opzoeken?

[Reinier]

Zit de TripActiviteit ook in Message?

[the-edge]

Reinier schreef op vrijdag 20 december 2019 @ 14:35:
Zit de TripActiviteit ook in Message?

Ja.
Ik was te snel.

Ja, er is een "TripActiviteitNr" (in TripActiviteit) en "TripActiviteit" (in Message)

[ Voor 26% gewijzigd door the-edge op 20-12-2019 14:37 ]

[Reinier]

the-edge schreef op vrijdag 20 december 2019 @ 14:35:
[...]


Ja.
Ik was te snel.

Ja, er is een "TripActiviteitNr" (in TripActiviteit) en "TripActiviteit" (in Message)

Dan moet je daarop joinen i.p.v. op trip. Toch?

[the-edge]

Reinier schreef op vrijdag 20 december 2019 @ 14:39:
[...]


Dan moet je daarop joinen i.p.v. op trip. Toch?

Eyeopener
Echter, krijg ik nu bij het uitvoeren van het volgende:

SQL:

SELECT
Tr.TripNr,
msg.Starttime

FROM Trip tr
LEFT JOIN TripActiviteit as act ON tr.Tripnr = act.Trip
LEFT JOIN Message as msg ON act.TripActiviteit = msg.tripActiviteitNr

WHERE
tr.TripNr = 12345

Bij msg.Starttime een "NULL"

[Reinier]

En met inner joins geen resultaten?

Geef eens de results van die losse query's die je in de OP postte?

[the-edge]

Reinier schreef op vrijdag 20 december 2019 @ 14:50:
En met inner joins geen resultaten?

Geef eens de results van die losse query's die je in de OP postte?

Klopt, een INNER join geeft geen resultaten.
Ik wil de results van de losse query posten, maar daar zit teveel informatie in om publiekelijk te delen.

Ik heb een paar kolommen meer die met elkaar 'matchen', moet ik op alle vergelijkbare kolommen joinen?

SQL:

LEFT JOIN Message as msg ON msg.activity = act.activity AND msg.trip = act.trip 

etc.?

[Rensjuh]

the-edge schreef op vrijdag 20 december 2019 @ 14:45:
[...]

Bij msg.Starttime een "NULL"

Denk dat je hier je probleem hebt.
Jij wil alleen resultaten waar StartTime != NULL ?
LEFT JOIN laat alle resultaten uit de 1e tabel zien, ook als er in de 2e tabel geen resultaten zijn.

Kun je eens je tabellen geven (met zo min mogelijk irrelevante kolommen)?
Ik krijg het even niet in m'n hoofd voor elkaar om je tabelopbouw (correct) te zien...

the-edge schreef op vrijdag 20 december 2019 @ 14:45:
[...]Ik heb een paar kolommen meer die met elkaar 'matchen', moet ik op alle vergelijkbare kolommen joinen?

SQL:

LEFT JOIN Message as msg ON msg.activity = act.activity AND msg.trip = act.trip 

etc.?

Nee, dit zou niet nodig hoeven te zijn.

[ Voor 26% gewijzigd door Rensjuh op 20-12-2019 15:02 ]

[rodie83]

Wat/hoe je moet joinen hangt natuurlijk af van je datamodel en dat datamodel kan ik niet zo raden

Zijn er meerdere tripactiviteiten/messages per tripnummer?

PS: Waarom gebruik je specifiek left join en niet gewoon join?

[ Voor 16% gewijzigd door rodie83 op 20-12-2019 15:03 ]

[ikweethetbeter]

Maak eens een ouderwets Entiteit Relatie Diagram.
Kan 1 trip meer dan 1 activiteiten hebben?
Kan 1 trip meer dan 1 berichten hebben?

Wat zijn hiervan de resultaten:

SQL:

SELECT *
FROM   Trip           tr
JOIN   TripActiviteit act ON act.Trip = tr.Tripnr
WHERE  tr.TripNr = 12345;

SELECT *
FROM   Trip           tr
JOIN   Message        msg ON msg.trip = tr.Tripnr
WHERE  tr.TripNr = 12345;

SELECT *
FROM   Trip           tr
JOIN   TripActiviteit act ON act.Trip = tr.Tripnr
JOIN   Message        msg ON msg.trip = tr.Tripnr
WHERE  tr.TripNr = 12345;

[MrMonkE]

Select Unique?


OT: Dat de foreign keys geen 'nr' hebben stoort me.

[the-edge]

MrMonkE schreef op vrijdag 20 december 2019 @ 15:20:
Select Unique?


OT: Dat de foreign keys geen 'nr' hebben stoort me.

Erger: geen foreign keys.
Ik ga bovenstaande tips proberen, ik zal vanavond meer inzicht in het datamodel geven.

ikweethetbeter schreef op vrijdag 20 december 2019 @ 15:09:
Maak eens een ouderwets Entiteit Relatie Diagram.
Kan 1 trip meer dan 1 activiteiten hebben?
Kan 1 trip meer dan 1 berichten hebben?

Wat zijn hiervan de resultaten:

SQL:

SELECT *
FROM   Trip           tr
JOIN   TripActiviteit act ON act.Trip = tr.Tripnr
WHERE  tr.TripNr = 12345;

SELECT *
FROM   Trip           tr
JOIN   Message        msg ON msg.trip = tr.Tripnr
WHERE  tr.TripNr = 12345;

SELECT *
FROM   Trip           tr
JOIN   TripActiviteit act ON act.Trip = tr.Tripnr
JOIN   Message        msg ON msg.trip = tr.Tripnr
WHERE  tr.TripNr = 12345;

Ja, 1 trip heeft altijd meerdere activiteiten en 1 trip heeft meerdere berichten.
Output van het 1e blokje bevat 12 rows, 2e blokje 31 rows en 3e blokje 372 rows. Ik zal inderdaad een ERD maken, ik verlies het overzicht een beetje

[ Voor 60% gewijzigd door the-edge op 20-12-2019 15:29 ]

[Reinier]

Je hoeft dus ""alleen maar" te achterhalen hoe een message aan een tripactiviteit hangt. En dat neem je mee in je join.
Bij gebrek aan sleutelvelden moet het misschien op tijd? Tijdstip van message tussen starttijd en eindtijd van de activiteit?

[the-edge]

Reinier schreef op vrijdag 20 december 2019 @ 15:59:
Je hoeft dus ""alleen maar" te achterhalen hoe een message aan een tripactiviteit hangt. En dat neem je mee in je join.
Bij gebrek aan sleutelvelden moet het misschien op tijd? Tijdstip van message tussen starttijd en eindtijd van de activiteit?

Volgens mij heb ik het nu voor een groot deel in orde. Het was even puzzelen.
Nu kom ik er wel meteen achter dat er ook activiteiten zijn zonder message. Dus dat is het volgende..

[The Eagle]

Is not null of <>' '
Afhankelijk van wat er echt in de tabel staat

[the-edge]

Het is gelukt, bedankt allen.

Een combinatie van een paar beginnersfouten:
- 'Verkeerde' rows in de join gebruikt
- Een fout in de WHERE waardoor de resultaten niet zuiver waren
- Te moeilijk denken, terug naar de logica

[The Eagle]

Als je nou nog eens zoiets hebt: maak voor jezelf een Venn diagram. Een plaatje helpt ontzettend