[MySQL] Gedupliceerde kolom namen in join query

[NickThissen]

Hoi,

Ik heb een Access database omgezet naar een MySQL database, en dat lijkt prima te werken. Ik loop nu echter tegen een probleem aan waar ik even niet omheen kan.

Als ik een join query (bijvoorbeeld een left join) uitvoer in Access, waarin twee of meer tabellen eenzelfde kolom naam hebben, dan krijg ik die kolommen netjes terug met naam "tabel.kolom", in plaats van enkel "kolom". In MySQL echter krijgen de kolommen allemaal gewoon de naam "kolom", en zitten er dus dubbele kolom namen in het resultaat!

Voorbeeld: tabel1 met kolom id en tabel2 met kolom id. Na een join als:

SQL:

SELECT tabel1.*, tabel2.* FROM (tabel1 LEFT JOIN tabel2 ON tabel1.id = tabel2.tabel1_id)

krijg ik van Access de volgende kolommen terug:

code:

tabel1.id, tabel2.id, tabel1_id

In MySQL krijg ik echter deze kolommen:

code:

id, id, tabel1_id

Ik kan in mijn code nu geen onderscheid maken van de twee kolommen met dezelfde naam, en vaak komt er dus verkeerde data in.


De meest voor de hand liggende oplossing is om alle kolommen apart in de query te vragen en ze allemaal een alias te geven (iets als "tabel_kolom"), zodat de query dan iets als dit wordt:

SQL:

SELECT tabel1.id AS tabel1_id, tabel2.id AS tabel2_id, tabel2.tabel1_id AS tabel2_tabel1_id FROM (tabel1 LEFT JOIN tabel2 ON tabel1.id = tabel2.tabel1_id)

Dit is echter geen oplossing voor mij, de queries worden automatisch gegenereerd, en de namen van de kolommen in de tabellen die gejoined worden (tabel2 in dit voorbeeld) weet ik dat op moment niet. Ik kan dus niks anders dan "tabel2.*" gebruiken.

Een andere oplossing is om de database te gaan door spitten en alle kolom namen een uniek voorvoegsel te geven (de tabelnaam), maar ook dat heeft niet echt mijn voorkeur...


Is er niet een andere manier waarop ik dit kan oplossen? Kan ik de database niet dwingen om (net als Access) automatisch de tabel naam er voor te zetten als twee kolommen dezelfde naam hebben?


Bedankt!

[beany]

Genereer je de query zelf? Of komt dat uit een 1 of ander component?

[RobIII]

beany schreef op zondag 15 april 2012 @ 16:28:
Genereer je de query zelf? Of komt dat uit een 1 of ander component?

NickThissen schreef op zondag 15 april 2012 @ 16:18:
Dit is echter geen oplossing voor mij, de queries worden automatisch gegenereerd, en de namen van de kolommen in de tabellen die gejoined worden (tabel2 in dit voorbeeld) weet ik dat op moment niet. Ik kan dus niks anders dan "tabel2.*" gebruiken.

En dat is dan meteen ook 't probleem; wat/wie/welk "genereert" voor jou die queries? Want daar zit je probleem. Dus of je gebruikt een gare (wannabe) ORM oid of je eigen code is ontbeert gewoon de nodige zaken.

[NickThissen]

Ik genereer de queries via mijn eigen code. Het gaat om een project wat ik lang geleden een keer geschreven heb en alleen af en toe wat update. Ik kan het wel aanpassen en het lukt vast wel om de queries op de juiste manier (met alias) te genereren, maar dat zal een tijd duren. Als er een andere oplossing is (zoals een instelling op de database waardoor hij (net als access) dit zelf doet bijvoorbeed?) die ik sneller kan gebruiken zou dat heel nuttig zijn... dan kan ik later nog altijd m'n code updaten. Dit is gewoon een gemis waar ik eerder helaas nooit tegenaan gelopen ben...

[RobIII]

Je generereert zeg je zelf de query; zolang je geen "select *" gebruikt maar daadwerkelijk de velden uitpoept (dus "select foo, bar, foo, ...") is het toch geen rocket science om te kijken of je al een veldnaam gebruikt hebt en dan, bijvoorbeeld, de tweede veldnaam te affixen met een _1 of beide foo's te prefixen met de tabelnaam?

[Gomez12]

NickThissen schreef op zondag 15 april 2012 @ 16:55:
Dit is gewoon een gemis waar ik eerder helaas nooit tegenaan gelopen ben...

Een db die je kolomnamen niet vernaggeld een gemis noemen...

Je zou ook gewoon eens naar de basis kunnen kijken en of je daar niet simpelweg de kolomnamen moet veranderen zodat je simpelweg overal weet waarover je het hebt

[RobIII]

Gomez12 schreef op zondag 15 april 2012 @ 17:33:
Je zou ook gewoon eens naar de basis kunnen kijken en of je daar niet simpelweg de kolomnamen moet veranderen zodat je simpelweg overal weet waarover je het hebt

Je suggereert nu DB-wide unieke veldnamen?

[leuk_he]

RobIII schreef op zondag 15 april 2012 @ 17:40:
[...]

Je suggereert nu DB-wide unieke veldnamen?

ja, voor alle colummen een (3 letterige ) prefix om aan te geven welke tabel het betreft. Wel vaker gezien. Voorkomt ook voor eens en altrijd dat een query niet meer werkt omdat je een kolom ergens toevoegt die ook ergens anders bestaat.

[NickThissen]

RobIII schreef op zondag 15 april 2012 @ 17:06:
Je generereert zeg je zelf de query; zolang je geen "select *" gebruikt maar daadwerkelijk de velden uitpoept (dus "select foo, bar, foo, ...") is het toch geen rocket science om te kijken of je al een veldnaam gebruikt hebt en dan, bijvoorbeeld, de tweede veldnaam te affixen met een _1 of beide foo's te prefixen met de tabelnaam?

Het probleem ligt bij het feit dat de code de kolommen van de tabellen waarop ik join niet weet. De kolommen van de 'hoofd' tabel (tabel1 in mijn voorbeeld) zijn wel bekend, maar van de tabellen waarop ik join worden enkel de namen doorgegeven (om de query op te bouwen). Blijkt nu dat dat niet genoeg is en ik ook de kolommen moet weten. Dat is niet onmogelijk uiteraard, maar de code is helaas net niet flexibel genoeg om dat 1-2-3 erin te bouwen. Dat zal waarschijnlijk wel de oplossing moeten worden, maar als er (in de tussentijd, om de database toch draaiende te houden) een snellere oplossing zou zijn zou ik die tijdelijk graag gebruiken.

Daarbij gebruik ik nu dus deze syntax: "SELECT tabel1.*, tabel2.*, tabel3.*, ...", en ik had eigenlijk gehoopt dat hij dan ook de kolommen in het resultaat met 'tabel1', 'tabel2' voorvoegt. Blijkbaar niet.

[ Voor 8% gewijzigd door NickThissen op 15-04-2012 17:52 ]

[beany]

leuk_he schreef op zondag 15 april 2012 @ 17:50:
[...]

ja, voor alle colummen een (3 letterige ) prefix om aan te geven welke tabel het betreft. Wel vaker gezien. Voorkomt ook voor eens en altrijd dat een query niet meer werkt omdat je een kolom ergens toevoegt die ook ergens anders bestaat.

En het voorkomt niet dat er brakke queries geschreven wordt. Het is luiheid.

Bij goede queries specificeer je de kolommen die je wilt hebben. Nooit doe je een 'select *'. Tenminste, zo is mij dat geleerd.

En 3 letterige prefixes zijn ook niet heilig. Met databases waar een paar honderd(of zelfs duizend) tabellen in zitten kom je al gauw in de knoei met je 3 lettertjes, terwijl de 3 letters dan vaak ook nog eens nergens op slaan omdat de combinaties die wel zinnig zijn allemaal al in gebruik zijn.

Kortom: select table.column is het beste om te doen. Op die manier haal je ook alleen de kolommen op die je moet hebben. Stel er komt een kolom bij waar binaire data in opgeslagen wordt? Plaatjes of zo. Zit je bij elke select tig MB aan extra data op te halen...

[Gomez12]

RobIII schreef op zondag 15 april 2012 @ 17:40:
[...]
Je suggereert nu DB-wide unieke veldnamen?

Niet perse db-wide uniek, maar een 3e normaal-vorm db met beschrijvende namen zal imho weinig doublures bevatten. Daar heb je geen 3x een "naam", daar heb je een persoonsnaam, een bedrijfsnaam en een productnaam bijv.
Je hebt niet 500x een kolom id, je hebt een productid, een personid, een companyid etc. etc. etc.

Met denormaliseren kan je weer doublures introduceren, maar daar maakt (als het goed is) niet welke kolom je pakt. Ze bevatten dezelfde info.

Tuurlijk zullen er vast nog wel een aantal doublures overblijven, maar dat is een paar aliassen in een paar query's.

leuk_he schreef op zondag 15 april 2012 @ 17:50:
[...]

ja, voor alle colummen een (3 letterige ) prefix om aan te geven welke tabel het betreft. Wel vaker gezien. Voorkomt ook voor eens en altrijd dat een query niet meer werkt omdat je een kolom ergens toevoegt die ook ergens anders bestaat.

Dan doe je hetzelfde als access. Je creeert unieke namen om het uniek zijn, lijkt me ook niet echt bevorderlijk.
Normaliseer je db en gebruik beschrijvende namen dat lijkt me veel handiger.

Als ik refereer naar een personId dan refereer ik altijd naar 1 veld, die manier van refereren kan ik best in 10 tabellen gebruiken omdat ik simpelweg 10 referenties heb naar 1 persoon (verspreid over 10 tabellen).
Ik vind het totaal onoverzichtelijk worden als ik die referentie op 10 plaatsen 10x anders noem.
Echter zie ik hier over het algemeen geen probleem in, of ik creeer een alias als ik het echt over iets anders heb, of ik selecteer in mijn query gewoon niet alle 10 de velden.

[Gomez12]

beany schreef op zondag 15 april 2012 @ 17:54:
[...]
Kortom: select table.column is het beste om te doen.

Ik denk dat dat simpelweg een kwestie van smaak is.

table.column is ook niet zaligmakend, 1 self inner join en je redt het al niet meer.

NickThissen schreef op zondag 15 april 2012 @ 17:50:
[...]
De kolommen van de 'hoofd' tabel (tabel1 in mijn voorbeeld) zijn wel bekend, maar van de tabellen waarop ik join worden enkel de namen doorgegeven (om de query op te bouwen). Blijkt nu dat dat niet genoeg is en ik ook de kolommen moet weten. Dat is niet onmogelijk uiteraard, maar de code is helaas net niet flexibel genoeg om dat 1-2-3 erin te bouwen.

Red je het niet met een extra query per join waarin je de kolomnamen opvraagt?
Het is niet mijn geprefereerde oplossing, maar wellicht is het een quick-fix. Ik kan me niet voorstellen dat die extra query iets anders dan superlicht is.

[beany]

Blijkbaar, als je een mysql_fetch_row() gebruikt krijg je een numeriek gebasseerde array terug(de kolommen zijn dus alleen met nummers te benaderen). Je kan dan met mysql_field_name() opvragen wat de naam van de kolom is, en met mysql_field_table() opvragen uit welke tabel de kolom komt.

Anders zou ik het ook niet weten eigenlijk...

[beany]

Gomez12 schreef op zondag 15 april 2012 @ 18:03:
[...]

Ik denk dat dat simpelweg een kwestie van smaak is.

table.column is ook niet zaligmakend, 1 self inner join en je redt het al niet meer.

Er zijn altijd uitzonderingen, maar in mijn ogen is het echt slecht om select * te gebruiken. Keertje een kolom toevoegen en alle code kan nagelopen worden, of je moet weer de regel hanteren dat kolommen alleen achter aan toegevoegd mogen worden, al heb je bij het verwijderen van een kolom dan alsnog een probleem.

[Gomez12]

beany schreef op zondag 15 april 2012 @ 18:04:
Blijkbaar, als je een mysql_fetch_row() gebruikt krijg je een numeriek gebasseerde array terug(de kolommen zijn dus alleen met nummers te benaderen). Je kan dan met mysql_field_name() opvragen wat de naam van de kolom is, en met mysql_field_table() opvragen uit welke tabel de kolom komt.

Anders zou ik het ook niet weten eigenlijk...

Simpelweg in de system-tabellen opvragen welke kolommen een table heeft en op basis daarvan kan je aliassen genereren.

beany schreef op zondag 15 april 2012 @ 18:06:
[...]
Er zijn altijd uitzonderingen, maar in mijn ogen is het echt slecht om select * te gebruiken. Keertje een kolom toevoegen en alle code kan nagelopen worden, of je moet weer de regel hanteren dat kolommen alleen achter aan toegevoegd mogen worden, al heb je bij het verwijderen van een kolom dan alsnog een probleem.

Tja, imho is select * slecht, maar query-resultaten benaderen op kolomnummers is dat imho ook.

De kosten van een versleten intellisense / autocomplete / keyboard zijn vele malen lager dan welk onderhoud ook wat er naderhand moet gebeuren als je een kolom toevoegt/verwijdert. De huidige IDE's hebben genoeg intelligence om niet meer in notepad elke toets-aanslag te moeten verantwoorden, gebruik dat dan ook.

[RobIII]

Gomez12 schreef op zondag 15 april 2012 @ 17:58:
Niet perse db-wide uniek, maar een 3e normaal-vorm db met beschrijvende namen zal imho weinig doublures bevatten. Daar heb je geen 3x een "naam", daar heb je een persoonsnaam, een bedrijfsnaam en een productnaam bijv.

Je hebt in de tabel product een veld name, in de tabel company een veld name en in de tabel person een veld name. Je hebt niet in de tabel product een productname, in company een companyname en in person een personname.
Dus zo:

SQL:

select person.name, company.name, ...
from person
inner join company....

vs

SQL:

select person.personname, company.companyname, ...
from person
inner join company....

Je gebruikt gewoon 'name' omdat die 'name' impliciet, doordat 'ie in de person tabel staat bijvoorbeeld, al op een person slaat en niet op een company. Of gebruik je ook een personzipcode, companyzipcode?

En dan ga je aliassen voor de paar keer dat je er niet omheen kunt omdat er her-en-der een keer een 'name' in zit:

SQL:

select p.name as personname, c.name as companyname, ...
from person p
inner join company c....

[ Voor 9% gewijzigd door RobIII op 15-04-2012 18:54 ]

[NickThissen]

Ik ben het wel eens met het nooit select * gebruiken, maar in mijn geval is het net wat genuanceerder, zoals ik al zei is het wel mogelijk om in plaats van "*" een opsomming met kolommen te geven, maar die functionaliteit zit er gewoon nog niet in. Het probleem is dat in de tussentijd m'n website (waar de database van is) stil heb gelegd. Ik heb namelijk bij het overstappen van access naar MySQL de database met een andere oudere database gemerged en wat opgeschoond enzo, en ik heb niet zoveel zin dat straks weer opnieuw te moeten gaan doen omdat er in de tussentijd mensen weer op de site dingen in de database aan het schrijven zijn. Bij het testen leek alles prima te werken tot dat ik ineens een raar resultaat zag (bij toeval gaat het echt maar op 1 pagina mis, en dan geen foutmelding ofzo maar dat ik gewoon verkeerde data krijg), dus nu ligt alles stil.

Ik hoopte dus dat er een snelle oplossing zou zijn die ik zou kunnen gebruiken om de site weer snel te kunnen laten draaien, tot m'n code verbetert is, maar het lijkt er niet op (anders had iemand die vast al genoemd ), dus ben ik ondertussen maar begonnen met het aanpakken van de code...


@Gomez12, ik ben het met RobIII eens, ik ga niet een kolom 'PersonName' noemen als de tabel al 'Person' (of Persons) heet. Met ID kan ik daar misschien nog inkomen, maar ook daar noem ik alles lekker gewoon Id en niet PersonId. Tenzij het natuurlijk een foreign key ofzo is in een andere tabel, dan is PersonId juist wel van toepassing, maar daar gaat het niet om.

Verder vind ik kolomnamen allemaal prefixen gaan geven ofzo ook niet echt netjes, onleesbaar en het zou niet nodig moeten zijn.

Nou ja, ik ga maar opschieten met m'n code

[DataGhost]

Gomez12 schreef op zondag 15 april 2012 @ 18:03:
[...]

Ik denk dat dat simpelweg een kwestie van smaak is.

Dat zeker, maar

table.column is ook niet zaligmakend, 1 self inner join en je redt het al niet meer.

dan wil ik graag zien hoe jouw opzet dit dan oplost en/of in welke situatie dit dan wel kan. Anders is het een beetje een non-argument.

[Verwijderd]

Als je niks constructiefs hebt toe te voegen, plaats dan gewoon een TR of zeg niks

[ Voor 79% gewijzigd door Creepy op 18-04-2012 17:25 ]

[Moraelyn]

Verwijderd schreef op maandag 16 april 2012 @ 11:15:
*snip*

Het probleem is niet dat hij geen kennis heeft van SQL, maar dat zijn code die kennis niet heeft ingebakken. Daar is hij nu mee bezig.

[ Voor 18% gewijzigd door Creepy op 18-04-2012 17:26 ]