[SQL] 2x bijna zelfde JOIN doen in WHERE

[ThinkPad]

Hoi,

Ik ben bezig met database te maken voor mezelf in MySQL. Maar het wil niet echt lukken met onderstaande query.

SQL:

SELECT * FROM item, locatie, in_bezit 
WHERE item.in_bezit_ID = in_bezit.ID 
AND item.locatie_ID = locatie.ID 
AND item.locatie_ID1 = locatie.ID 
ORDER BY datum_inkoop DESC

Ik heb dus 2x een locatie ID, die in de tabel "locatie" staat. Een inkoop locatie kan ook een verkoop locatie zijn en vice versa.

De query geeft geen foutmelding, maar hij geeft 0 rijen terug, terwijl het wel werkt als ik de regel

SQL:

AND item.locatie_ID1 = locatie.ID 

weghaal.

[ Voor 23% gewijzigd door ThinkPad op 20-07-2011 23:20 ]

[frostraver]

er zullen geen items zijn die aan de vergelijking voldoen dan waarschijnlijk. De code is alleszins helemaal juist hoor. Ik zie geen probleem verder.

[ThinkPad]

In mijn geval is "item.locatie_ID" een '3', en "item.locatie_ID1" een '4'

[Voutloos]

Dan moet je nogmaals met locatie joinen.

En hernoem kolommen met slechte namen als id1 en gebruik geen komma voor joins maar schrijf het expliciet uit inc ON clause

[RobIII]

ThinkPadd schreef op woensdag 20 juli 2011 @ 23:26:
In mijn geval is "item.locatie_ID" een '3', en "item.locatie_ID1" een '4'

En toch staat in je query 2x "foo = locatie.ID". Maar MySQL moet maar snappen dat je de ene keer 3 en de andere keer 4 bedoelt

Verder: Schrijf joins bij voorkeur uit (dus "inner/left/right/outer join foo on...") en voor je eigenlijke probleem gebruik je gewoon een alias.

SQL:

select f.a, f.b, f.c, b1.x, b1.y, b2.x, b2.y
from foo as f
inner join bar as b1 on f.fk1 = b1.id
inner join bar as b2 on f.fk2 = b2.id
order by f.a

[ Voor 35% gewijzigd door RobIII op 20-07-2011 23:47 ]

[Verwijderd]

Afhankelijk van de exacte gegevens, denk ik dat je met de volgende query al geholpen bent:

SQL:

SELECT * FROM item, locatie, in_bezit  
WHERE item.in_bezit_ID = in_bezit.ID  
AND (
item.locatie_ID = locatie.ID  
OR item.locatie_ID1 = locatie.ID 
)
ORDER BY datum_inkoop DESC

De query die je nu gebruikt zoekt naar een locatie waarbij zowel locatie_ID en locatie_ID1 hetzelfde zijn, terwijl deze verschillen. Met mijn voorbeeld zoek je naar een locatie met een ID die in locatie_ID of locatie_ID1 staat.

[coldasice]

Waarom zet je er niet een koppeltabel tussen?

item-itemlocatie-locatie


daarmee kun je oneindig veel items op locatie hebben, de structuur van itemlocatie is dan
itemID
locatieID

beide sleutel....kun je er ook nog specifieke eigenschappen bij zetten, bijvoorbeeld inkoop of verkoop of voorkeurlocatie of zoiets....

[RobIII]

coldasice schreef op donderdag 21 juli 2011 @ 09:40:
Waarom zet je er niet een koppeltabel tussen?

Ja... en nee
In sommige gevallen is 1:n helemaal niet van toepassing; op basis van de gegevens die we uit TS hebben kunnen we daar weinig over zeggen dus ik zou niet al te zwart/wit stellen dat een koppeltabel nodig is

Verwijderd schreef op donderdag 21 juli 2011 @ 09:11:

SQL:

item.locatie_ID = locatie.ID  
OR item.locatie_ID1 = locatie.ID 

Die matched als 1 van beiden waar is; heel iets anders dan de TS nodig heeft. Heb je 't voor de gein al eens geprobeerd?

Verder ben ik er niet zo'n fan van als oplossingen al voorgekauwd en wel aangedragen worden (dat zag ik je al eerder doen):

Give a man a fish and feed him for a day. Teach a man how to fish and feed him for a lifetime.

Misschien dat je daar een beetje rekening mee wil houden Ik ben blij met je enthousiasme en behulpzaamheid maar van dit soort posts steekt een TS (uiteindelijk) niets op (los van 't feit of je post/oplossing correct is of niet).

[ Voor 63% gewijzigd door RobIII op 21-07-2011 10:43 ]

[coldasice]

RobIII schreef op donderdag 21 juli 2011 @ 10:37:
[...]

Ja... en nee
In sommige gevallen is 1:n helemaal niet van toepassing; op basis van de gegevens die we uit TS hebben kunnen we daar weinig over zeggen dus ik zou niet al te zwart/wit stellen dat een koppeltabel nodig is

nodig niet, maar het is een kleine moeite en het is een veel nettere oplossing.

[RobIII]

coldasice schreef op donderdag 21 juli 2011 @ 11:04:
[...]

nodig niet, maar het is een kleine moeite en het is een veel nettere oplossing.

Dat is nou precies wat ik beweer: het hoeft geen nettere oplossing te zijn; een koppeltabel is in veel gevallen nodig (wanneer je 1:n hebt). In andere gevallen heeft een entiteit gewoon 2 FK's naar eenzelfde tabel. Bij een, ik verzin effe wat, telefoongesprek heb je bijv. een source en destination. Daar komt gewoon nooit een 3^e bij kijken want dat is onmogelijk. Beide velden zouden, in theorie, prima een FK naar een adres tabel kunnen zijn (bijvoorbeeld). Even los van 't feit dat dit niet 's werelds beste voorbeeld is: je ziet dat hier geen sprake is van 1:n.

Of als je een tabel "persoon" zou hebben; je hebt maar 1 vader en 1 moeder. Dat zijn gewoon 2 FK's; geen koppeltabel. TS heeft er nu voor gekozen deze velden "locatie" en "locatie1" te noemen oid, maar had 't "foo" en "bar" geheten was 't veel minder duidelijk geweest dat er misschien een koppeltabel nodig is.

Gewoon normaliseren en dan komt er vanzelf uit of je een koppeltabel nodig hebt of niet.

[ Voor 34% gewijzigd door RobIII op 21-07-2011 11:18 ]

[Remus]

RobIII schreef op donderdag 21 juli 2011 @ 11:12:
[...]
Bij een, ik verzin effe wat, telefoongesprek heb je bijv. een source en destination. Daar komt gewoon nooit een 3^e bij kijken want dat is onmogelijk. Beide velden zouden, in theorie, prima een FK naar een adres tabel kunnen zijn (bijvoorbeeld). Even los van 't feit dat dit niet 's werelds beste voorbeeld is: je ziet dat hier geen sprake is van 1:n.

Offtopic: Het is inderdaad een slecht voorbeeld, want three-way calling is mogelijk (al zou het technisch kunnen dat je daarvoor twee telefoonlijnen nodig hebt).

[RobIII]

Remus schreef op donderdag 21 juli 2011 @ 11:57:
[...]


Offtopic: Het is inderdaad een slecht voorbeeld, want three-way calling is mogelijk (al zou het technisch kunnen dat je daarvoor twee telefoonlijnen nodig hebt).

Uiteindelijk zijn 't gewoon meerdere gesprekken die, toevallig, allemaal naar dezelfde "chatbox" bellen Ieder gesprek heeft dus z'n eigen source, alleen hebben ze toevallig allemaal dezelfde destination.

[Verwijderd]

Toch zou ik ook in het geval van een telefoonlijn (en ook in het geval van de OP) kiezen voor een koppeltabel. Je queries worden er een stuk overzichtelijker van. OP hoeft niet twee keer te joinen op dezelfde locatie-tabel; met telefoonlijnen hoef je niet moeilijk te doen om vragen te beantwoorden als "welke lijnen hebben een verbinding met een bepaald punt (ongeacht of dat punt de source of de destination is)". Allemaal extra functionaliteit, die je bijna gratis voor niets erbij krijgt!

[RobIII]

Verwijderd schreef op donderdag 21 juli 2011 @ 12:25:
Allemaal extra functionaliteit, die je bijna gratis voor niets erbij krijgt!

En je krijgt er ook extragratis bij dat je datamodel niet kan garanderen dat er maar 1 source en 1 destination is (tenzij je een derde veld en een constraint in de koppeltabel introduceert, of met triggers ofzo gaat werken) En dan is 't dus "up to the businesslogic" om te zorgen dat een gesprek altijd maar 1 src en 1 dst heeft. Dat maakt 't niet overzichtelijker noch makkelijker te onderhouden en introduceert zelfs kans op fouten (want: bugs).

Verwijderd schreef op donderdag 21 juli 2011 @ 12:25:
"welke lijnen hebben een verbinding met een bepaald punt (ongeacht of dat punt de source of de destination is)".

Huh?

SQL: Mijn suggestie (2 FK's)

select foo, bar, foobar, src, dst
from calls
where (src = x) or (dst = x)

Vs:

SQL: Jouw suggestie (1:n)

select foo, bar, foobar, callsrcdst.nr as src, callsrcdst.nr as dst
from calls
inner join callsrcdst on callsrcdst.callid = calls.id
where (callsrcdst.nr = x)

En dan nemen we nog maar even voor lief dat mijn suggestie gegarandeerd maar 1 record teruggeeft per call die voldoet aan de criteria en de jouwe dus (mogelijk) meer. Dat kun je oplossen door, zoals ik zei, een derde veld te introduceren en een unique constraint te leggen, maar daar wordt je query niet duidelijker op:

SQL:

select foo, bar, foobar, callsrcdst.nr as src, callsrcdst.nr as dst
from calls
inner join callsrcdst on callsrcdst.callid = calls.id
where (callsrcdst.nr = x) and ((callsrcdst.type = 'src') or (callsrcdst.type = 'dst'))

[ Voor 51% gewijzigd door RobIII op 21-07-2011 14:05 ]

[Verwijderd]

RobIII schreef op donderdag 21 juli 2011 @ 12:35:
[...]

En je krijgt er ook extragratis bij dat je datamodel niet kan garanderen dat er maar 1 source en 1 destination is (tenzij je een derde veld en een constraint in de koppeltabel introduceert, of met triggers ofzo gaat werken) En dan is 't dus "up to the businesslogic" om te zorgen dat een gesprek altijd maar 1 src en 1 dst heeft. Dat maakt 't niet overzichtelijker noch makkelijker te onderhouden en introduceert zelfs kans op fouten (want: bugs).

Het klopt dat het datamodel niet kan garanderen dat er maar 1 source en 1 destination is. En inderdaad zal de business logic dat moeten afvangen. Business logic is alleen wel eenvoudiger aan te passen dan een datamodel. Dus mocht de requirement ooit in de toekomst veranderen, dan ben je met een koppeltabel beter af. Toegegeven, bij een telefoonlijn zal dit niet snel het geval zijn, hoewel conference calls een voorbeeld zijn waarbij je al trucjes moet gaan uithalen om aan de "1 source, 1 destination" restrictie te blijven voldoen.

In het geval van de OP ligt het waarschijnlijk meer voor de hand om een koppeltabel te gebruiken, aangezien de kolomnamen al suggeren dat het om een opsomming van ongerelateerde locaties gaat.

SQL: Jouw suggestie (1:n)

select foo, bar, foobar, callsrcdst.nr as src, callsrcdst.nr as dst
from calls
inner join callsrcdst on callsrcdst.callid = calls.id
where (callsrcdst.nr = x)

Alle lijnen die verbinding hebben met een bepaald punt:

SQL:

select distinct lijn_id
from lijn_punt_connectie
where punt_id = x

En je hebt helemaal gelijk, als je wilt weten wat de source en destination is van iedere lijn, dan wordt de query wel een stuk complexer. Bij nader inzien zou ik in het geval van telefoonlijnen inderdaad de source en destination opnemen in de calls tabel, aangezien ze een integraal onderdeel vormen van de connectie.

In het geval de OP, waar je een lijst van (twee) locaties hebt, zou ik zeker voor een koppeltabel gaan.

[ Voor 0% gewijzigd door RobIII op 21-07-2011 15:32 . Reden: Quote tag gefixed. ]

[RobIII]

Verwijderd schreef op donderdag 21 juli 2011 @ 14:58:
Dus mocht de requirement ooit in de toekomst veranderen, dan ben je met een koppeltabel beter af.

In mijn (andere) voorbeeld van een vader & moeder "FK" in een personstabel: zie je die requirements ooit veranderen? Kies je dan ook/toch nog voor een koppeltabel?

Verwijderd schreef op donderdag 21 juli 2011 @ 14:58:
Toegegeven, bij een telefoonlijn zal dit niet snel het geval zijn, hoewel conference calls een voorbeeld zijn waarbij je al trucjes moet gaan uithalen om aan de "1 source, 1 destination" restrictie te blijven voldoen.

Helemaal niet: een call heeft altijd maar 1 source en 1 destination; hoe kun je nou een call opzetten met 2 destinations? Of 2 sources? Dat je 2 calls opzet en die als "één" hoort is puur een stukje "audio mixing". Net zoals een webpagina vele resources (afbeeldingen, CSS, JS) van vele bronnen/domeinen kan bevatten: uiteindelijk zie je maar 1 webpagina; elk afzonderlijk deel is echter maar van 1 bron afgehaald en naar 1 doel toe gegaan (je browser).

Verwijderd schreef op donderdag 21 juli 2011 @ 14:58:
In het geval van de OP ligt het waarschijnlijk meer voor de hand om een koppeltabel te gebruiken, aangezien de kolomnamen al suggeren dat het om een opsomming van ongerelateerde locaties gaat.

Die suggestie wekt 'ie inderdaad maar daar is ook alles mee gezegd. Om dan meteen stellig te beweren dat een koppeltabel nodig is vind ik gewoon te zwart/wit; precies mijn initiële punt.

Verwijderd schreef op donderdag 21 juli 2011 @ 14:58:
In het geval de OP, waar je een lijst van (twee) locaties hebt, zou ik zeker voor een koppeltabel gaan.

Voor de "honderste" keer: Zoals ik al zei kan dat de juiste oplossing zijn, maar het kan ook de verkeerde zijn. We weten helemaal niet genoeg (TS vertelt te weinig concreets) over wat nou de bedoeling is en waar 't daadwerkelijk over gaat om zo'n statement dat je nu maakt te kunnen maken. All we know is dat 'ie 2 FK's naar een locatie tabel heeft. En dat kan zoals ik aantoon(de) de juiste oplossing zijn. Net zo goed als een koppeltabel de juiste oplossing kan zijn. Dus "zeker voor een koppeltabel gaan" is behoorlijk kort door de bocht. Precies wat ik in eerste instantie zei en waardoor deze hele discussie ontstond.

[ Voor 57% gewijzigd door RobIII op 21-07-2011 15:31 ]

[coldasice]

RobIII schreef op donderdag 21 juli 2011 @ 15:17:

Helemaal niet: een call heeft altijd maar 1 source en 1 destination; hoe kun je nou een call opzetten met 2 destinations? Of 2 sources? Dat je 2 calls opzet en die als "één" hoort is puur een stukje "audio mixing". Net zoals een webpagina vele resources (afbeeldingen, CSS, JS) van vele bronnen/domeinen kan bevatten: uiteindelijk zie je maar 1 webpagina; elk afzonderlijk deel is echter maar van 1 bron afgehaald en naar 1 doel toe gegaan (je browser).

technisch maakt het niet uit hoe de verbinding opgezet wordt, als je de gegevens van die conference call wilt opslaan wil je alle nummers opslaan en omdat je niet weet om hoeveel bellers het gaat, maak je een koppeltabel.

[RobIII]

coldasice schreef op donderdag 21 juli 2011 @ 16:45:
[...]


technisch maakt het niet uit hoe de verbinding opgezet wordt, als je de gegevens van die conference call wilt opslaan wil je alle nummers opslaan en omdat je niet weet om hoeveel bellers het gaat, maak je een koppeltabel.

Als je wil weten wie er in die conference call zit kun je ook iedereen opvragen die dezelfde destination heeft (ik roep maar wat); daarbij is het maar de vraag of je een conference-call niet bijv. z'n eigen tabel geeft met daaraan weer een "deelnemers" tabel. Ik zou dan sowieso kiezen voor een eigen entiteit; alle deelnemers an-sich hebben dan nog steeds gewoon calls met een src en dst.

Maar laten we kappen met nitpicken en doorzagen over die calls; het was misschien niet 's werelds beste voorbeeld. Mijn punt moge inmiddels wel duidelijk zijn; is het dat niet dan heb je een aardige plaat beton voor je hoofd

[ Voor 9% gewijzigd door RobIII op 21-07-2011 16:56 ]

[ThinkPad]

Zo! Al flink wat reacties, waarvoor dank.

Het zijn inderdaad gewoon 2 FK's, en een koppeltabel ken ik ook, maar dat leek mij niet de juiste oplossing. Ik wil gewoon 2x een ID van een locatie in een record van een item op kunnen slaan, zodat ik overige info uit de tabel "locaties" kan halen.

Ik ga even verder prutsen.

[Voutloos]

In dat geval was mijn reactie al voldoende antwoord.

[ThinkPad]

Ik heb nu dit

SQL:

select *  
from item, locatie, in_bezit 
inner join in_bezit on item.in_bezit_ID = in_bezit.ID 
inner join locatie as b1 on item.locatie_ID = b1.ID 
inner join locatie as b2 on item.locatie_ID1 = b2.ID 
order by datum_inkoop DESC

Maar dan krijg ik in MySQL de foutmelding: #1066 - Not unique table/alias: 'in_bezit'

??

[ Voor 8% gewijzigd door ThinkPad op 22-07-2011 13:14 ]

[RobIII]

Je from klopt niet...

[ThinkPad]

Kun je iets specifieker zijn? Ik snap dat je het antwoord niet wil voorkauwen, wat ik ook begrijp Maar ik zie niet in waar het fout gaat, ik noem toch de 3 tabellen die ik verderop gebruik?

[coldasice]

ThinkPadd schreef op vrijdag 22 juli 2011 @ 13:08:
Ik heb nu dit

SQL:

select *  
from item as i, locatie as l, in_bezit as ib
inner join in_bezit on i.in_bezit_ID = ib.ID 
inner join locatie on i.locatie_ID = l.ID 
inner join locatie on i.locatie_ID = l2.ID 
order by datum_inkoop DESC

Maar dan krijg ik in MySQL de foutmelding: #1066 - Not unique table/alias: 'locatie'

??

ik begrijp eigenlijk ook niet waarom je van die aliases gebruikt, die maakt de leesbaarheid alleen maar lastiger. Je kiest nette namen zodat je het goed kunt lezen,dat doe je goed en daarna ga je lelijke aliassen gebruiken. Vooral omdat je i en l door elkaar gebruikt....

Waar je volgens mij de fout in gaat is dat je je item tabel als 2 verschillende aliasen moet gebruiken, omdat je de item table 2 keer verschillend gebruikt


select *
from item as i, locatie as l, in_bezit as ib,item as i2
inner join in_bezit on i.in_bezit_ID = ib.ID
inner join locatie on i.locatie_ID = l.ID
inner join locatie on i.locatie_ID2 = l2.ID
order by datum_inkoop DESC

[Acid_Burn]

tabellen die je joined zet je niet in je from anders gebruik je ze dubbel... wat ook weer uit de foutmelding volgt

[RobIII]

Acid_Burn schreef op vrijdag 22 juli 2011 @ 13:22:
tabellen die je joined zet je niet in je from anders gebruik je ze dubbel... wat ook weer uit de foutmelding volgt

Juist; in de from hoort maar 1 tabel te staan en ik mis, zie ik nu, juist op de joins (beide "locatie" joins) waar het nodig is een alias (loc1 en loc2 ofzo)

ThinkPadd schreef op vrijdag 22 juli 2011 @ 13:20:
ik noem toch de 3 tabellen die ik verderop gebruik?

Die hoef je niet te noemen; dat doe je al in de join

ThinkPadd schreef op vrijdag 22 juli 2011 @ 13:20:
Kun je iets specifieker zijn?

Doe eens gek en pak er de documentatie eens bij als je niet zeker bent van de syntax.

[ Voor 41% gewijzigd door RobIII op 22-07-2011 13:47 ]

[ThinkPad]

Ik heb nu dit, en dat werkt.

SQL:

SELECT * 
FROM item
INNER JOIN in_bezit ON item.in_bezit_ID = in_bezit.ID
INNER JOIN locatie AS loc1 ON item.locatie_ID = loc1.ID
INNER JOIN locatie AS loc2 ON item.locatie_ID = loc2.ID
ORDER BY datum_inkoop DESC 

Maar wat ik nu eigenlijk wil, is dat ik in m'n code (PHP) de naam die bij een locatie ID ophaal.

Ik doe nu gewoon

PHP:

while($row = mysql_fetch_assoc($result)) {
        $row = escapeArray($row);
$row["naam"]

maar dan laat hij 2x dezelfde naam zien. Hoe kan ik zorgen dat hij de loc1 en loc2 namen allebei laat zien? Ipv 2x dezelfde (loc1).

Ik weet niet goed hoe ik het onderscheid moet maken tussen loc1 en loc2 in de code. naam.loc1 werkt namelijk niet.

[ Voor 14% gewijzigd door ThinkPad op 22-07-2011 14:40 ]

[RobIII]

SQL:

select foo, bar, loc1.veldX as Blaat, loc2.veldX as Woei
....

En nu is 't wel welletjes; meer handjes houden gaan we hier niet meer doen.

[ Voor 5% gewijzigd door RobIII op 22-07-2011 14:43 ]

[ThinkPad]

Oja tuurlijk, "AS"

Sorry papa RobIII