[SQL] complexe inner/outer JOIN - Softwareontwikkeling

woensdag 15 oktober 2003 13:32

Acties:

Verwijderd

Topicstarter

code:

SELECT A.ID AS ID_ADVERTENTIE, G.NAAM_USER, G.PLAATS_USER, 
Merk.ID AS MERK, 
Type.ID AS TYPE, 
Extra.ID AS EXTRA 
FROM Advertentie A 
JOIN Gebruiker G ON A.ID = G.ID_USER 
JOIN Auto Merk ON A.ID_AUTO = Merk.ID 
LEFT OUTER JOIN Auto Type ON Merk.ID = Type.VOLGID 
LEFT OUTER JOIN Auto Extra ON Type.ID = Extra.VOLGID 
WHERE (Merk.ID = A.ID AND Merk.IDVOLG IS NULL) 
OR (Type.ID = A.ID AND Type.VOLGID = Merk.ID AND Type.ID = Extra.VOLGID) 
OR (Extra.ID = A.ID AND Extra.VOLGID = Type.ID)

In deze query maak ik waarschijnlijk een fout bij de WHERE conditie. Het is eenmaal lastig om na te gaan welk kind bij welke ouder hoort. Dat wil ik in de query duidelijk maken.

Deze code is enkel voorbeeld, de code waar ik aan werk mag niet gepost worden, dus je antwoord moet met behulp van dit voorbeeld komen. Het is lastig uit te leggen, ik ben er ook al uren mee bezig, maar krijg nog steeds niet het gewenste resultaat.

Hoe gaat deze query wel werken???

[ Voor 3% gewijzigd door whoami op 15-10-2003 13:58 . Reden: query onleesbaar ]

woensdag 15 oktober 2003 13:36

Acties:

whoami

Kan je eens de lay-out van je 2de query verzorgen, want zo is het echt niet leesbaar.
Als je wilt dat mensen er naar kijken, dan zorg je best voor een wat leesbaardere layout

https://fgheysels.github.io/

woensdag 15 oktober 2003 13:57

Acties:

Verwijderd

Topicstarter

De opmaak werkt niet zo te zien, helaas....
toch wel, maar dan moet je de code-tags goed gebruiken.

[ Voor 49% gewijzigd door whoami op 15-10-2003 13:59 ]

woensdag 15 oktober 2003 14:00

Acties:

whoami

Je joined wel zeer raar ....

Waar staat de tabel EXTRA gedefinieerd? Ik zie wel een alias van een veld die Extra heet, maar geen tabel?

Een self join kan je zo verwezenlijken:

code:

1
2
3

SELECT *
FROM  auto a1
JOIN auto a2 ON a2.parent_id = a1.id

edit:

/me heeft te snel gelezen

Wat wil je trouwens verwezenlijken met die WHERE condities?

Je joined daar nog eens?

[ Voor 22% gewijzigd door whoami op 15-10-2003 14:05 ]

https://fgheysels.github.io/

woensdag 15 oktober 2003 14:08

Acties:

prutsger

Wat ik altijd doe bij lastige queries is in Access in design view instellen zoals ik het wil hebben en dan de sql te kopieren.

Eventueel nog iets aanpassen en klaar! Weet niet of dit je een beetje helpt, maar vind ik altijd het makkelijkst.

@whoami: ja gooi dat topic maar dicht, valt niet mee he op het goede knopje klikken

woensdag 15 oktober 2003 14:11

Acties:

Verwijderd

Topicstarter

De recursie moet 2 diep gaan, vandaar dat ik Type (van table Auto) LEFT OUTER JOIN op Extra (ook van table Auto). Met jou voorbeeldje heb je maar 1 diepte.

In mijn SELECT query gebruik ik een WHERE omdat er anders waarden in de verkeerde kolommen terecht komen.

Volgens mij gaat het niet lukken zo...

[ Voor 40% gewijzigd door Verwijderd op 15-10-2003 14:22 ]

woensdag 15 oktober 2003 15:12

Acties:

Poiter

De query klopt sowieso niet, heb je waarschijnlijk niet gecopieerd.

SELECT A.ID AS ID_ADVERTENTIE, G.NAAM_USER, G.PLAATS_USER,
Merk.NAAM AS MERK,
Type.NAAM AS TYPE,
Extra.NAAM AS EXTRA
FROM Advertentie A
JOIN Gebruiker G ON A.ID_USER = G.ID
JOIN Auto Merk ON A.ID_AUTO = Merk.ID
LEFT OUTER JOIN Auto Type ON Merk.ID = Type.VOLGID
LEFT OUTER JOIN Auto Extra ON Type.ID = Extra.VOLGID

Zo moet ie beter zijn

De where clause heb je niet nodig, want de verbinding wordt al door de join gelegt.
Wat is het reslutaat hier van?

woensdag 15 oktober 2003 15:26

Acties:

Defspace

Administrator

Niet echt een antwoord. Maar waarom normaliseer je die auto table niet ?
Vind ik zelf wat joins betreft een stuk overzichtelijker werken ?
Om nog maar niet te spreken over de voordelen in functionaliteit en leesbaarheid van je database ?

offtopic:
Bij mij heeft Mysql het best zwaar met 22500 queries op 1 pagina :-)

woensdag 15 oktober 2003 15:37

Acties:

drm

f0pc0dert

wtf doet dit in 1 tabel

ID | NAAM | VOLGID
1 | BMW | NULL
2 | 3 serie | 1
3 | 4 drs | 2

Music is the pleasure the human mind experiences from counting without being aware that it is counting
~ Gottfried Leibniz

woensdag 15 oktober 2003 15:40

Acties:

curry684

left part of the evil twins

drm schreef op 15 October 2003 @ 15:37:
wtf doet dit in 1 tabel

[...]

Uhm ja dat is duidelijk een belabberd design waar geen query tegen opgewassen is of zal blijven

Professionele website nodig?

woensdag 15 oktober 2003 15:41

Acties:

Verwijderd

drm schreef op 15 October 2003 @ 15:37:
wtf doet dit in 1 tabel

[...]

mooie normalisatie idd

het is misschien verstandig om je datamodel eens grondig aan te passen

woensdag 15 oktober 2003 15:58

Acties:

Verwijderd

Topicstarter

De table Auto normaliseer ik niet, omdat de diepte nog niet is vastgesteld. Er kan een één op veel relatie gemaakt worden op een record. Hier is bewust voor gekozen, dit kan ik niet veranderen. Bij elke diepte die er eventueel bij komt, komt er een kolom bij.

Zonder die WHERE clause komen er kinderen en ouders in de verkeerde kolommen (Merk, Type, Extra) terecht. Met de SELECT wil ik nagaan of de ID in de Advertentie een ouder, kind of kleinkind is. Met de WHERE dacht ik de ouders in de eerste table Merk, kinderen in table Type en kleinkinderen in table Extra te krijgen. Zonder die WHERE zet hij kleinkinderen zowel in table Merk en Type. Dus bijvoorbeeld:

ID | MERK | TYPE | EXTRA
3 | BMW | 3 serie | 4 drs
3 | 3 serie | 4 drs | NULL
3 | 4 drs | NULL | NULL

De eerste record is juist weergegeven, omdat de advertentie ID = 3.

woensdag 15 oktober 2003 16:06

Acties:

drm

f0pc0dert

De table Auto normaliseer ik niet, omdat de diepte nog niet is vastgesteld. Er kan een één op veel relatie gemaakt worden op een record. Hier is bewust voor gekozen, dit kan ik niet veranderen. Bij elke diepte die er eventueel bij komt, komt er een kolom bij.

Wat lijkt je handiger: een database die goed in elkaar zit waar je nooit meer wat bij hoeft te maken (tabel, kolom, whatever) of een brak ontwerp waarmee je queries moet bouwen die onleesbaar zijn en waarschijnlijk nooit foutloos worden

Voorstelletje:
merk
merk_id
naam

type
type_id
merk_id
naam

auto
auto_id
merk_id
type_id
opmerkingen

extra
extra_id
omschrijving

auto_extra (koppeltabel)
auto_id
extra_id

Zoiets. Eenvoudiger schoolvoorbeeld kun je bijna niet bedenken. Dan heb je helemaal niets met "niveau's" of iets van die strekking te maken. (Ik snap sowieso niet wat je daar mee bedoelt).

Music is the pleasure the human mind experiences from counting without being aware that it is counting
~ Gottfried Leibniz

woensdag 15 oktober 2003 16:06

Acties:

curry684

left part of the evil twins

Ik heb geen idee wat je aan het doen bent, maar mag ik de eerlijke vraag stellen of je uberhaupt zelf denkt dat je datamodel ergens op slaat?

Professionele website nodig?

woensdag 15 oktober 2003 16:56

Acties:

Poiter

In je advertentie-table komen bij id_auto toch wel alleen de echte auto-id's voor hé en niet ook merk en extra-id's ?

Voer je query eens uit zonder die beide left outer joins, dus:

SELECT A.ID AS ID_ADVERTENTIE, G.NAAM_USER, G.PLAATS_USER,
Merk.NAAM AS MERK,
Type.NAAM AS TYPE,
Extra.NAAM AS EXTRA
FROM Advertentie A
JOIN Gebruiker G ON A.ID_USER = G.ID
JOIN Auto Merk ON A.ID_AUTO = Merk.ID

wat is het resultaat?

woensdag 15 oktober 2003 18:35

Acties:

Verwijderd

Topicstarter

Ik weet dat de reacties een beetje vreemd zijn, maar ik weet echt wel waar ik mee bezig ben. Er zijn meerdere mogelijkheden en die wil ik eens proberen.

Poiter, in de advertentie table komt of de Merk ID, of de Type ID of de Extra ID van de table Auto voor. Dus de SELECT moet ook uitzoeken bij welke Extra het Type hoort etc etc...

jajaja hij is lastig mensen

woensdag 15 oktober 2003 18:48

Acties:

Defspace

Administrator

Verwijderd schreef op 15 October 2003 @ 18:35:
Ik weet dat de reacties een beetje vreemd zijn, maar ik weet echt wel waar ik mee bezig ben. Er zijn meerdere mogelijkheden en die wil ik eens proberen.

Poiter, in de advertentie table komt of de Merk ID, of de Type ID of de Extra ID van de table Auto voor. Dus de SELECT moet ook uitzoeken bij welke Extra het Type hoort etc etc...

jajaja hij is lastig mensen

Tja, aangezien je wel weet wat joins zijn zou je haast denken dat je wel weet waar je mee bezig bent.
Maar misschien kan je toch iets meer vertellen over de achterliggende gedachte achter dit "datamodel" want die snap ik dus helemaal niet ?

Vooral deze quote doet mij weer denken dat je niet weet waar je mee bezig bent ?

De table Auto normaliseer ik niet, omdat de diepte nog niet is vastgesteld. Er kan een één op veel relatie gemaakt worden op een record. Hier is bewust voor gekozen, dit kan ik niet veranderen. Bij elke diepte die er eventueel bij komt, komt er een kolom bij.

Bij elke diepte komt er een kolom bij ? Welja, we voegen gewoon ff een kolommetje toe. Is dat handig ? Niet echt!
Daarbij houd niets je bij een genormaliseerde database toch tegen om 1 op veel relaties te maken ?

woensdag 15 oktober 2003 20:40

Acties:

Poiter

Verwijderd schreef op 15 October 2003 @ 18:35:
Ik weet dat de reacties een beetje vreemd zijn, maar ik weet echt wel waar ik mee bezig ben. Er zijn meerdere mogelijkheden en die wil ik eens proberen.

Poiter, in de advertentie table komt of de Merk ID, of de Type ID of de Extra ID van de table Auto voor. Dus de SELECT moet ook uitzoeken bij welke Extra het Type hoort etc etc...

jajaja hij is lastig mensen

Hmm, dan wordt het inderdaad een stuk ingewikkelder, en zou ik zeker eens gaan kijken of er geen andere oplossing is. Wil je het toch oplossen met een query, dan krijg je 3 UNION query's. Dit wordt dus iets als :

SELECT A.ID AS ID_ADVERTENTIE, G.NAAM_USER, G.PLAATS_USER,
Merk.NAAM AS MERK,
Type.NAAM AS TYPE,
Extra.NAAM AS EXTRA
FROM Advertentie A
JOIN Gebruiker G ON A.ID_USER = G.ID
JOIN Auto Merk ON A.ID_AUTO = Merk.ID
LEFT OUTER JOIN Auto Type ON Merk.ID = Type.VOLGID
LEFT OUTER JOIN Auto Extra ON Type.ID = Extra.VOLGID
WHERE Merk.VOLGID IS NULL
UNION
SELECT A.ID AS ID_ADVERTENTIE, G.NAAM_USER, G.PLAATS_USER,
Merk.NAAM AS MERK,
Type.NAAM AS TYPE,
Extra.NAAM AS EXTRA
FROM Advertentie A
JOIN Gebruiker G ON A.ID_USER = G.ID
JOIN Auto Type ON A.ID = Type.ID
JOIN Auto Merk Type.VOLGID = Merk.ID
LEFT OUTER JOIN Auto Extra ON Type.ID = Extra.VOLGID
WHERE Merk.VOLGID IS NULL
UNION
SELECT A.ID AS ID_ADVERTENTIE, G.NAAM_USER, G.PLAATS_USER,
Merk.NAAM AS MERK,
Type.NAAM AS TYPE,
Extra.NAAM AS EXTRA
FROM Advertentie A
JOIN Gebruiker G ON A.ID_USER = G.ID
JOIN Auto Extra ON A.ID = Extra.ID
JOIN Auto Type ON Extra.ID = Type.VOLGID
JOIN Auto Merk Type.VOLGID = Merk.ID
WHERE Merk.VOLGID IS NULL

Er kunnen nog wat fouten inzitten, maar zoiets moet het zijn.
Maar met je ontwerp zit het niet goed, zeker wat uitbreidbaarheid betreft.
Voor elke kolom die je erbij maakt in je auto-tabel krijg je een extra UNION, en elke aparte query krijgt een extra join. Bij kleine tabellen gaat dat misschien nog maar bij grote tabellen wordt dit een performance-killer.

woensdag 15 oktober 2003 21:09

Acties:

jvdmeer

Nou, vooruit dan, deze is getest:

SQL:

SELECT Gebruiker.Plaats, Gebruiker.Naam, MerkTypeExtra.Merk, MerkTypeExtra.Type,
       MerkTypeExtra.Extra, Advertentie.Omschrijving
FROM Advertentie
  LEFT OUTER JOIN Gebruiker 
    ON Advertentie.ID_User = Gebruiker.ID
  LEFT OUTER JOIN MerkTypeExtra
    ON Advertentie.ID_Auto = MerkTypeExtra.ID

waarbij voor de duidelijkheid gebruik is gemaakt van de volgende view:

SQL:

CREATE VIEW MerkTypeExtra (ID,Merk,Type,Extra) AS
(
  SELECT Extra.ID, Merk.Naam, Type.Naam, Extra.Naam
    FROM auto extra 
    INNER JOIN auto Type
      ON Extra.VolgID = Type.ID 
    INNER JOIN auto Merk
      ON Type.VolgID = Merk.ID
)
UNION
(
  SELECT Type.ID, Merk.Naam, Type.Naam, NULL
    FROM auto Type 
    INNER JOIN auto Merk 
      ON Type.VolgID = Merk.ID 
      WHERE Merk.VolgID IS NULL
)
UNION
(
  SELECT ID,Naam,null,null
    FROM auto 
    WHERE VolgID is NULL
)

Maarreh... doe eens wat aan je data model. Bijvoorbeeld het schoolvoorbeeld van drm.

donderdag 16 oktober 2003 08:46

Acties:

Verwijderd

Topicstarter

Ik doe het liever ook op een andere manier, maar deze methode moet ik in mijn programma toepassen. Het programma is een opdracht voor mijn studie. Waarschijnlijk zal ik dit gedeelte in het programma moeten aanpassen. Alhoewel ik al een stuk verder ben gekomen dan eerst...