[sql] Query vraagje

[djlinsen]

Ik heb 3 tabellen
tabel a
ID
INFO

tabel b
AID
CID

tabel c
CID
MEERINFO

Table b is een koppel tabel tussen a en c.
Tabel b koppelt de info tussen a en c op de volgende wijze:
AID CID
1 1
5 2
13 3
Dit betekend dat record uit a met AID 1 t/m 4 bij CID 1 horen en AID 5t/m12 bij CID 2 enz.
Ik wil nu in een query van alle AID's uit a de bijbehorende CID hebben, hoe kan ik dit het beste aanpakken?

Alvast Bedankt

[P_de_B]

Programming FAQ - SQL - Hoe werken joins?

In de Faq staat het duidelijker dan dat ik zou kunnen omschrijven

[MacWebber]

Ik zou beginnen met degene die dit datamodel verzonnen heeft tegen de muur te zetten.
Daarna zou ik maar eens kijken of je niet gewoon (zoals het hoort) de tussenliggende records in tabel B ook kunt vullen, dus zodat je dit krijgt:

AID CID
1 1
2 1
3 1
4 1
5 2
6 2
etc.

Dan kun je namelijk normale joins gebruiken om te komen tot wat je zoekt. Als je dat niet wilt doen, zul je vanuit je AID moeten gaan zoeken naar het eerst hogere CID en vervolgens met > en < in je join de boel aan elkaar moeten knopen. Behalve dat het kwa performance waarschijnlijk een drama wordt, ga je dit ook nooit meer snappen als je er over 2 maanden weer eens in moet duiken.

[MacWebber]

P_de_B schreef op vrijdag 07 december 2007 @ 11:24:
Programming FAQ - SQL - Hoe werken joins?

In de Faq staat het duidelijker dan dat ik zou kunnen omschrijven

Volgens mij heb jij er net zo kort naar gekeken als ik, voor ik mijn reactie wilde tikken

[Jochemmol]

Je probleem is me niet helemaal duidelijk. Maar het lijkt mij vrij basic.

Waarom zet je het CID niet in tabel a? Dat scheeld.

En wat wil je nu precies zien? Als je je mail lees dan denk ik dat je het volgende wil:

code:

AID   CID
1       1
2       1
3       1      
4       1
5       2
6       2
7       2
8       2
9       2
10     2
11     2
12     2
13     3

Volgens mij kan je dan het beste uit tabel c alle ID's selecteren en dan a via een inner join toevoegen aan je query.

code:

// Zoiets als dit
select AID,CID from c
INNER JOIN b
    ON b.CID=c.CID
INNER JOIN a
   ON a.AID=b.AID

[djlinsen]

Tja het datamodel heb ik niet verzonnen, kan ik ook niets aan wijzigen. Het op de juiste wijze vullen van tabel b kan ook niet, ik mag namelijk niets aan de database zelf veranderen. Alleen een query dus

Ik heb naar de joins gekeken, maar ik kan daar niet uit afleiden hoe ik die in mijn geval zou moeten gebruiken. Er is namelijk geen fysieke koppeling tussen een aantal records in A en in C, De koppeling die er wel is, is dat vanaf AID in B tot het volgende record in AID bij de bijbehorende CID hoort.

@jochemmol, dat is dus precies wat ik wil zien, alleen begrijp ik nog niet helemaal hoe jij dat met de 2 innerjoins nou voorelkaar wilt krijgen.

[Creepy]

Dit betekend dat record uit a met AID 1 t/m 4 bij CID 1 horen en AID 5t/m12 bij CID 2 enz.

Weet je dit 100% zeker? Dit is nogal, eeh, vreemd. Normaal gesproken wordt er per rij in je koppeltabel ook echt maar 1 koppeling bedoelt en is er niks range en order afhankelijk.

Het datamodel lijkt me verder prima, alleen de interpretatie van de koppeltabel niet.

[ Voor 13% gewijzigd door Creepy op 07-12-2007 11:44 ]

[djlinsen]

Ja dit weet ik 100% zeker, Ik had ook graag gewoon een koppeltabel b gezien met daarin voor elk AID het bijbehorende CID. Helaas is het even niet anders. Performance is daarom ook even niet zo heel belangrijk, in een later stadium gaan we waarschijnlijk toch wel het een en ander wijzigen.

[P_de_B]

MacWebber schreef op vrijdag 07 december 2007 @ 11:26:
[...]


Volgens mij heb jij er net zo kort naar gekeken als ik, voor ik mijn reactie wilde tikken

idd

[justmental]

Het wordt een puinhoop, maar zoiets kan misschien als je dbms het ondersteunt:

code:

select a.id, a.info, (select c.meerinfo from c where c.id = (select b.cid from b where a.id = (select max(b2.aid) from b b2 where b2.aid <= a.id)))
from a

[Jochemmol]

ow wacht ik begrijp je verkeert

in tabel b staat niet:

code:

AID CID
1     1
2     1
3     1
enz

AID       CID
1t/m 5  1
6t/m 12 2
enz

nee dan kan het niet met mijn query.
Klopt dit wat ik zeg?

[djlinsen]

justmental schreef op vrijdag 07 december 2007 @ 11:50:
Het wordt een puinhoop, maar zoiets kan misschien als je dbms het ondersteunt:

code:

select a.id, a.info, (select c.meerinfo from c where c.id = (select b.cid from b where a.id = (select max(b2.aid) from b b2 where b2.aid <= a.id)))
from a

puinhoop maakt me even niet zo veel uit, dat is een belangrijk puntje voor latere zorg. Ik ga even met jou oplossing aan het klooien.

Edit:
Ik heb even het een en ander zitte proberen, maar ik krijg met jou query de volgende melding (mssql) tijdens execution van de query (Syntax wordt wel netjes geaccepteerd)
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

[ Voor 24% gewijzigd door djlinsen op 07-12-2007 12:10 ]

[Creepy]

En na een foutmelding verwachten we nog wat extra informatie over wat je zelf al hebt geprobeerd om die fout weg te krijgen. De melding lijkt me vrij duidelijk
Het is niet de bedoeling een stuk van iemand te kopieren/plakken, de foutmelding terug te geven en te hopen dat wij het voor je gaan rechttrekken, wat extra inzet tonen zien we graag

[4of9]

de syntax wordt niet geaccepteerd want in je laatste subquery staat een <= en dat mag niet in een subquery (dat kan namelijk meer dan 1 resultaat opleveren) dat is overigens ook wat de foutmelding zegt.

[djlinsen]

Creepy schreef op vrijdag 07 december 2007 @ 12:13:
En na een foutmelding verwachten we nog wat extra informatie over wat je zelf al hebt geprobeerd om die fout weg te krijgen. De melding lijkt me vrij duidelijk
Het is niet de bedoeling een stuk van iemand te kopieren/plakken, de foutmelding terug te geven en te hopen dat wij het voor je gaan rechttrekken, wat extra inzet tonen zien we graag

Makkelijker gezegd dan gedaan, Ik begrijp de melding verder wel: een subquery mag niet meer dan 1 record als resultaat terug geven. Alleen hoe krijg ik dat dan voor elkaar? Mijn kennis over sql is een basic, subquery's heb ik nooit veel mee gedaan omdat ik het tot nu toe allemaal zonder af kon.
Naar mijn idee leverd de laatste subquery met de max(b2.aid) in iedergeval altijd maar 1 record als resultaat. Dus het probleem zit hem in de 1e en of 2e subquery of mogelijk zouden de records in de tabel niet helemaal kloppen volgens de beschrijving die ik gegeven heb. Dit laatste heb ik echter even uitgesloten door alleen naar de eerste 100 records van a te kijken, die heb ik handmatig even gecontrolleerd en kloppen met mijn beschrijving.

Verder heb ik het idee dat de opzet van de query zowieso niet helemaal goed is, maar door beperkte kennis van sql lukt het me niet om de vinger op de zere plek van de aangedrage query te leggen.

4of9 simpel weg het < teken weghalen had ik al meteen geprobeerd, dit leverd echter de zelfde foutmelding op, syntax is niets mis mee, het probleem is dat de subquery meer dan 1 record terug geeft.

[jvdmeer]

Vooruit dan, weer een beetje uitdaging:

SQL:

select a.*, c.* from a
join b b1 on a.id>=b1.aid
left join b b2 on (b2.cid=b1.cid+1  or b2.cid is null) and a.id<b2.aid 
join c on b1.cid=c.cid
where b2.aid is not null or b1.aid=(select max(aid) from b)

Getest en al

Enige voorwaarde hier is dat CID netjes oploopt, zonder gaten er in. Maar ook daar moet omheen te werken zijn.

[ Voor 16% gewijzigd door jvdmeer op 07-12-2007 14:31 ]