[SQL] Onmogelijke JOIN?

[ygma!l]

Ok, ik heb een lastig probleempje, het is als volgt:
Ik heb 3 tabellen, die op de juiste manier 'gelinkt' moeten worden, ik heb de tabellen zelf aangemaakt.

De tabellen zien er als volgt uit:
tblTables

code:

-ID [integer]
-TblName [VARCHAR]
-TblDesc [VARCHAR]

tblColumns

code:

-ID [integer]
-ColName [VARCHAR]
-ColDesc [VARCHAR]
-TblID [integer]

tblColumnLink

code:

-ID [integer]
-ColID [integer]
-ColIDPrim [integer]

Ok, even wat aanvullende info: tblColumns.TblID = tblTables.ID, tblColumnLink.ColID = tblColumns.ID en tblColumnLink.ColIDPrim = tblColumns.ID.

Waarvoor dienen de tabellen en waarvoor gaat het dienen... Goed, het is de bedoeling dat de tabel tblTables wordt gevuld met tabel namen(lijkt me duidelijk). Daarnaast moet tblColumns gevuld gaan worden met kolom namen en moet d.m.v. een tblID 'gekoppeld' worden aan een tabel. De tblColumnLink is even een rare tabel misschien. Deze is bedoeld om een relatie te kunnen aangeven tussen twee kolommen uit 2 verschillende tabellen...

Wat is nu precies mijn probleem? Stel de tabellen zijn als volgt gevuld:
TblTables

ID	tblName	tblDesc
1	tblContracten	Contracten tabel
2	tblBedrijven	Bedrijven tabel
3	tblKlanten	Klanten tabel

TblColumns

ID	ColName	ColDesc	TblID
1	Contractnr	Contract nummer	1
2	BedrijfID	Bedrijf nummer	1
3	BedrijfID	Bedrijf nummer	2
4	Bedrijfnaam	Bedrijf naam	2
5	Klantnr	Klantnummer	3
6	Klantnaam	Klant naam	3

TblColumnLink

ID	ColID	ColIDPrim
1	2	3

Nu wil ik graag de volgende output krijgen (op basis van de bovenstaande gegevens):

• Contractnr
• BedrijfID
• BedrijfNaam

Dat wil ik graag krijgen op basis van TblID=1 (in dit geval), de relaties moet SQL dus zelf leggen d.m.v. de link tabel.

Nu ben ik zelf aan het stoeien geweest met sql, maar ik kom er niet uit... Ik kreeg vaak alleen de kolom namen uit tabel 1 of alleen bijv. alleen 'BedrijfID'.

Hieronder een eigen brouwsel in SQL, die helaas niet werkt:

code:

SELECT C.ColName FROM tblColumns C INNER JOIN tblTables T ON C.TblID=T.ID LEFT JOIN tblColumnLink CL ON C.ID=CL.ColID WHERE C.TblID=1 OR C.ID=CL.ColIDPrim

Graag zou ik een oplossing willen horen... misschien is er iets niet goed in mijn tabel structuur of doe ik iets niet goed in mijn sql code?

[Verwijderd]

Nu wil ik graag de volgende output krijgen (op basis van de bovenstaande gegevens):

* Contractnr
* BedrijfID
* BedrijfNaam


Dat wil ik graag krijgen op basis van TblID=1 (in dit geval), de relaties moet SQL dus zelf leggen d.m.v. de link tabel.

De eerste 2 snap ik, maar hoezo wil je BedrijfNaam er ook bij op basis van TblID=1?

Ik denk dat ik het al snap... Column 2 is 'gekoppeld' aan column 3?

Ik vind het er nogal wazig uit zien, maar het 'zou' moeten kunnen. Als het klopt wat hierboven staat iig...?

[ Voor 20% gewijzigd door Verwijderd op 07-02-2006 11:51 ]

[Lethalis]

Enigzins offtopic, maar waarom kies je voor een meta database?

Het maakt alles vaak alleen maar onnodig complex.

[ygma!l]

Verwijderd schreef op dinsdag 07 februari 2006 @ 11:50:
[...]

De eerste 2 snap ik, maar hoezo wil je BedrijfNaam er ook bij op basis van TblID=1?

Ik denk dat ik het al snap... Column 2 is 'gekoppeld' aan column 3?

Ik vind het er nogal wazig uit zien, maar het 'zou' moeten kunnen. Als het klopt wat hierboven staat iig...?

Ja, column 2 is 'gekoppeld' aan column 3.

Lethalis schreef op dinsdag 07 februari 2006 @ 11:56:
Enigzins offtopic, maar waarom kies je voor een meta database?

Het maakt alles vaak alleen maar onnodig complex.

Ik snap even niet wat je bedoeld, maar ik ga toch nog even proberen uit te leggen waarvoor dit allemaal is.


Het is de bedoeling dat: er een soort van rapporten systeem komt. Ik heb hier een database met veel tabellen me verschillende kolommen die met elkaar gelinkt zijn. Het moet zo worden dat de gebruiker niet zomaar uit al deze tabellen/kolommen kan kiezen. Daarom wil ik voorgedifineerd een aantal kolommen in een 'database' hebben staan. Dit om het systeem te vertellen welke kolommen/tabellen er gebruikt mogen worden. Daarnaast moet in deze 'database' bijgehouden worden welke kolommen er (in de daadwerklijke database) gekoppeld zijn aan elkaar. Nu moet het zo worden dat wanneer de gebruiker kiest voor een bepaalde tabel, dat het systeem automatisch de onderliggende tabellen (incl. kolommen) moet laten zien. (d.m.v. die linktabel). Ik hoop dat ik het nu iets duidelijker heb gemaakt met wat ik graag wil bereiken.

[Verwijderd]

ygma!l schreef op dinsdag 07 februari 2006 @ 12:19:
[...]

Ja, column 2 is 'gekoppeld' aan column 3.

Is het dan niet handiger om de koppeling op tabelbasis te doen ipv kolombasis??

[ygma!l]

Verwijderd schreef op dinsdag 07 februari 2006 @ 12:20:
[...]

Is het dan niet handiger om de koppeling op tabelbasis te doen ipv kolombasis??

Misschien is dit wel een oplossing, echter weet het systeem dan niet meer welke kolommen bij elkaar horen en uiteindelijk in de rapport query 'ge-joint' moeten worden.

[Buffy]

Als ik het goed begrijp wil je dus de de column namen van tabellen waarvan de tabel tblContracten een foreignkey heeft.

Deze SQL query zou de ID's van de tabellen moeten opleveren:

code:

SELECT C2.TblID
FROM ((TblColumn C1 INNER JOIN TblColumnLink CL ON C1.ID = CL.CollID)
            INNER JOIN TblColumn C2 ON C2.ID = CL.CollIDPrim)
WHERE C1.tblID = 1

Note: levert waarschijnlijk dubbele ID's op als je tabel twee foreignkeys heeft naar de zelfde tabel.

Hiermee kan je column namen van de gelinkte tabellen opzoeken (extra JOIN).
Als je ook de column namen van contracten tabel zou je een UNION of een OR
moeten gebruiken.

Misschien werkt dit:

code:

SELECT TblColumns.ColName
FROM TblColumns
WHERE (TblColumns.TblID = 1 OR
              TblColumns.TblID IN (
                      SELECT C2.TblID
                      FROM ((TblColumns C1 INNER JOIN TblColumnLink CL ON C1.ID = CL.CollID)
                                  INNER JOIN TblColumns C2 ON C2.ID = CL.CollIDPrim)
                      WHERE C1.tblID = 1))

Note: als een tabel een foreignkey naar zich zelf heeft levert dit echter dubbele column namen op.
Note2: Als de geneste SELECT per TblColumns record wordt uitgevoerd is een UNION misschien efficiënter.

[ACM]

ygma!l schreef op dinsdag 07 februari 2006 @ 11:31:
Nu wil ik graag de volgende output krijgen (op basis van de bovenstaande gegevens):

• Contractnr
• BedrijfID
• BedrijfNaam

Dat wil ik graag krijgen op basis van TblID=1 (in dit geval), de relaties moet SQL dus zelf leggen d.m.v. de link tabel.

Nu ben ik zelf aan het stoeien geweest met sql, maar ik kom er niet uit... Ik kreeg vaak alleen de kolom namen uit tabel 1 of alleen bijv. alleen 'BedrijfID'.

Als ik het goed begrijp wil je dus met die drie tabellen hierboven beschrijvingen van relaties die gebruikt moeten worden in de query zelf. Dat is dus wat eerder met meta database bedoelt werd.

Je beschrijft in je database wat er in een andere database zit. Die beschrijvingen heb je nodig om je queries te kunnen maken/uitvoeren. Echter kan SQL niet de inhoud van kolommen behandelen alsof het identifiers binnen de query zijn. Dus je kan niet de waarde van het veld "tblTables.TblName" direct in de query gebruiken alsof het een tabelnaam is.

Wat je wel zou kunnen doen is de inhoud van die tabellen bij elkaar zoeken. En aan de hand van de diverse records in je applicatie een nieuwe query genereren, die daarna gebruikt wordt om je daadwerkelijk gewenste gegevens op te zoeken.

[ygma!l]

Dawns_sister schreef op dinsdag 07 februari 2006 @ 13:16:
Als ik het goed begrijp wil je dus de de column namen van tabellen waarvan de tabel tblContracten een foreignkey heeft.

Deze SQL query zou de ID's van de tabellen moeten opleveren:

code:

SELECT C2.TblID
FROM ((TblColumn C1 INNER JOIN TblColumnLink CL ON C1.ID = CL.CollID)
            INNER JOIN TblColumn C2 ON C2.ID = CL.CollIDPrim)
WHERE C1.tblID = 1

Note: levert waarschijnlijk dubbele ID's op als je tabel twee foreignkeys heeft naar de zelfde tabel.

Ja, dit is wel wat ik wil, echter werk jouw bovengenoemde code niet, alhoewel ik het idee heb dat de code er dicht op zit. Ik heb zelf al een paar dingen daarmee geprobeerd, echter kreeg ik nog geen enkel resultaat.

ACM schreef op dinsdag 07 februari 2006 @ 13:30:
[...]

Als ik het goed begrijp wil je dus met die drie tabellen hierboven beschrijvingen van relaties die gebruikt moeten worden in de query zelf. Dat is dus wat eerder met meta database bedoelt werd.

Je beschrijft in je database wat er in een andere database zit. Die beschrijvingen heb je nodig om je queries te kunnen maken/uitvoeren. Echter kan SQL niet de inhoud van kolommen behandelen alsof het identifiers binnen de query zijn. Dus je kan niet de waarde van het veld "tblTables.TblName" direct in de query gebruiken alsof het een tabelnaam is.

Wat je wel zou kunnen doen is de inhoud van die tabellen bij elkaar zoeken. En aan de hand van de diverse records in je applicatie een nieuwe query genereren, die daarna gebruikt wordt om je daadwerkelijk gewenste gegevens op te zoeken.

Het klopt dat ik hiermee beschrijf wat er in een andere database zit, echter een deel ervan. Die beschrijvingen heb ik nodig zodat de gebruiker uiteindelijk in de applicatie eenvoudig de keuze krijgt voorgeschoteld welke kolommen hij/zij in het rapport wil hebben. De relaties wil ik ook in de database hebben zodat de applicatie (d.m.v. een sql query) aangeeft welke kolommen er geselecteerd kunnen op basis van een tabel en niet dat de gebruiker willkeurige tabellen/kolommen kan selecteren die niet met elkaar samenhangen. Ik hoop dat dit duidelijk is.

[Buffy]

ygma!l schreef op dinsdag 07 februari 2006 @ 13:42:
[...]

Ja, dit is wel wat ik wil, echter werk jouw bovengenoemde code niet, alhoewel ik het idee heb dat de code er dicht op zit. Ik heb zelf al een paar dingen daarmee geprobeerd, echter kreeg ik nog geen enkel resultaat.

Probeer dan eerst eenvoudiger select statements en bouw die telkens een stapje uit zodat je kan zien waar het fout gaat. Bv eerst:

code:

SELECT C1.ID
FROM TblColumns C1
WHERE C1.tblID = 1

en dan

code:

SELECT CL.CollIDPrim
FROM (TblColumns C1 INNER JOIN TblColumnLink CL ON C1.ID = CL.CollID)
WHERE C1.tblID = 1

Vaak zit je je blind te staren op een typefout of een verkeerde waarde in de database. En dan is dit de enige manier om die fouten op te sporen


PS: Zie nu dat ik TblColumn ipv TblColumns gebruikte. Is dat misschien de fout?

[ Voor 6% gewijzigd door Buffy op 07-02-2006 14:25 ]

[ygma!l]

Dawns_sister schreef op dinsdag 07 februari 2006 @ 14:23:
[...]
PS: Zie nu dat ik TblColumn ipv TblColumns gebruikte. Is dat misschien de fout?

Ja, klopt dit had ik al gezien. Nee, ik had een foutje gemaakt in de database waardoor de query niet werkte, sorry... De sql code werkt prima, maar ik heb nog een wens.

code:

SELECT DISTINCT TblColumns.ColName
 FROM TblColumns
 WHERE (TblColumns.TblID = 1 OR
              TblColumns.TblID IN (
                      SELECT C2.TblID
                      FROM ((TblColumns C1 INNER JOIN TblColumnLink CL ON C1.ID = CL.ColID)
                                  INNER JOIN TblColumns C2 ON C2.ID = CL.ColIDPrim)
                      WHERE C1.tblID = 1))

Stel dat ik de database als volgt gevuld zou hebben:
TblTables

ID	tblName	tblDesc
1	tblContracten	Contracten tabel
2	tblBedrijven	Bedrijven tabel
3	tblKlanten	Klanten tabel

TblColumns

ID	ColName	ColDesc	TblID
1	Contractnr	Contract nummer	1
2	BedrijfID	Bedrijf nummer	1
3	BedrijfID	Bedrijf nummer	2
4	Bedrijfnaam	Bedrijf naam	2
5	Klantnr	Klantnummer	2
6	Klantnr	Klantnummer	3
7	Klantnaam	Klant naam	3

TblColumnLink

ID	ColID	ColIDPrim
1	2	3
2	5	6

Dan wil ik graag dat alle 'kolommen' uit de 'klanten tabel' ook zichtbaar worden. (De klanten tabel zit dus weer genest in de bedrijven tabel zeg maar). Ik weet dat dit niet logisch is, maar het gaat even om het scenario. Ik wil dus eigenlijk dat het systeem dynamisch genoeg is om dit ook te kunnen behappen.

[Buffy]

ygma!l schreef op dinsdag 07 februari 2006 @ 15:10:
[...]

Ja, klopt dit had ik al gezien. Nee, ik had een foutje gemaakt in de database waardoor de query niet werkte, sorry... De sql code werkt prima, maar ik heb nog een wens.

code:

SELECT DISTINCT TblColumns.ColName
 FROM TblColumns
 WHERE (TblColumns.TblID = 1 OR
              TblColumns.TblID IN (
                      SELECT C2.TblID
                      FROM ((TblColumns C1 INNER JOIN TblColumnLink CL ON C1.ID = CL.ColID)
                                  INNER JOIN TblColumns C2 ON C2.ID = CL.ColIDPrim)
                      WHERE C1.tblID = 1))

Stel dat ik de database als volgt gevuld zou hebben:

[..knip..]

Dan wil ik graag dat alle 'kolommen' uit de 'klanten tabel' ook zichtbaar worden. (De klanten tabel zit dus weer genest in de bedrijven tabel zeg maar). Ik weet dat dit niet logisch is, maar het gaat even om het scenario. Ik wil dus eigenlijk dat het systeem dynamisch genoeg is om dit ook te kunnen behappen.

Daar was ik al bang voor je. Je wilt een query die uitgaande van een tabel alle tabel colummen levert die direct en indirect te berijken zijn via de realties zoals vastgelegd in de TblColumnLink tabel.

Dat gaat je niet lukken met SQL, tenminsten niet dynamisch. Dat wil je ook niet want als je een cycle in je database hebt zou je query in een loop terecht komen.

Ben bang dat de enige mogelijkheid is functie te schrijven die recursief herhaaldelijk de eerste query uitvoert en de TblID's in een tijdelijke tabel bijhoudt en checked of er een loop gevonden is.

Dan kan je ook de relatie tot het begin record bijhouden. Als je bv in de Bedrijf tabel twee foreignkeys naar een Persoon tabel hebt (vb Contactpersoon en Tekenbevoegdpersoon) dan kan je de gebruiker ipv uit twee keer Naam laten kiezen uit Bedrijf.Contactpersoon.Naam en Bedrijf.Tekenbevoegdpersoon.Naam.

de verzamel query wordt dan zoiets als:

code:

SELECT C1.ColDesc,C2.TblID
FROM ((TblColumn C1 INNER JOIN TblColumnLink CL ON C1.ID = CL.CollID)
            INNER JOIN TblColumn C2 ON C2.ID = CL.CollIDPrim)
WHERE C1.tblID = 1

[ygma!l]

Dawns_sister schreef op dinsdag 07 februari 2006 @ 15:31:
[...]
*knip*

Bedankt voor je hulp, het is me uiteindelijk gelukt d.m.v. een programeer taal, die kijkt tot hoever ze 'gelinkt' zijn. De uiteindlijke output bestaat dan uit de tblID's.