[SQL] Probleem met LEFT JOIN

[Verwijderd]

Beste mensen,

Ik ben bezig met een programma voor een bedrijf waarmee orders verwerkt kunnen worden. De database ziet er zo uit:

Ik heb een tabel prijslijst:
Artikelcode | Merk | Verkoopprijs (etc...)
-----------------------------------------
AA-001-DD | product1 | 1,95
AA-001-DE | product2 | 2,05

Een tabel prijslijstperrelatie
Artikelcode | Debiteurnr | Verkoopprijs
------------------------------------------------
AA-001-DD | 955 | 1,89
AA-001-DE | 899 | 1,99

En nog een tabel orderinfo, maar die is voor deze query niet van belang.

Mijn vraag:
Ik wil een query waarin de tabel prijslijst volledig getoond wordt en waarnaast de aangepaste prijs (uit de tabel prijslijstperrelatie) getoond wordt (indien deze aanwezig is voor de opgegeven debiteur). Dus bijvoorbeeld:

Ik maak een prijslijst voor debiteur 955:
Artikelcode | prijslijst.verkoopprijs: | prijslijstperrelatie.verkooppr:
----------------------------------------------------------------------------------
AA-001-DD | 1,95 | 1,89
AA-001-DE | 2,05 | ISNULL

Een voorbeeld:
Het bedrijf heeft voor sommige debiteuren een aangepaste verkoopprijs. Deze prijs staat in de tabel prijslijstperrelatie.
Dus volgens bovenstaande tabel betaalt debiteur 955 een aangepaste prijs van 1,89 voor het artikel met code AA-001-DD (product1). Echter, debiteur 955 betaalt 2,05 voor het artikel met code AA-001-DE (immers, het debiteurnr 955 icm met artikelcode AA-001-DE komt niet voor in de tabel prijslijstperrelatie, dus wordt de standaard prijs berekend)

Het volgende had ik zelf al geprobeerd, maar dit werkte helaas niet:

code:

SELECT p.artikelcode, p.merk, p.verpakkingshoeveelheid, 
p.verpakkingseenheid, p.inkoopprijs, p.verkoopprijs, r.verkoopprijs 

FROM prijslijst p 
LEFT JOIN prijslijstperrelatie r ON p.artikelcode=r.artikelcode 

WHERE r.debiteurnr=
(SELECT debiteurnr FROM orderinfo WHERE ordernr=1084)

[P_de_B]

Wat werkt er precies niet? Krijg je een foutmelding of onverwachte resultaten?

[Verwijderd]

Ik krijg onverwachte resultaten: met bovenstaande query worden slechts de producten weergegeven waarvan een aangepaste prijs is ingevoerd (hij moet dus alle producten weergegeven, en -indien aanwezig- de aangepaste prijs)

[glashio]

code:

SELECT p.artikelcode, p.merk, p.verpakkingshoeveelheid, 
p.verpakkingseenheid, p.inkoopprijs, p.verkoopprijs, r.verkoopprijs 

FROM prijslijst p 
LEFT JOIN prijslijstperrelatie r ON p.artikelcode=r.artikelcode 

WHERE r.debiteurnr IN
(SELECT debiteurnr FROM orderinfo WHERE ordernr=1084)

Volgens mij was het zo.

IN i.p.v. = ... omdat je met een verzameling terug kan krijgen

[P_de_B]

Verwijderd schreef op vrijdag 29 april 2005 @ 14:29:
Ik krijg onverwachte resultaten: met bovenstaande query worden slechts de producten weergegeven waarvan een aangepaste prijs is ingevoerd (hij moet dus alle producten weergegeven, en -indien aanwezig- de aangepaste prijs)

Dat klopt omdat je in je WHERE statement refereert naar een kolom in de OUTER gejoinde tabel.

code:

LEFT OUTER JOIN prijslijstperrelatie r ON p.artikelcode=r.artikelcode AND r.DebiteurNr = [nummer]

Dit moet volgens mij wel werken

[Verwijderd]

Sorry voor mn late reactie, ik was even wat aan het prutsen...Maar goed, ik heb jullie ideeen geprobeerd...

glashio schreef op vrijdag 29 april 2005 @ 14:29:

code:

SELECT p.artikelcode, p.merk, p.verpakkingshoeveelheid, 
p.verpakkingseenheid, p.inkoopprijs, p.verkoopprijs, r.verkoopprijs 

FROM prijslijst p 
LEFT JOIN prijslijstperrelatie r ON p.artikelcode=r.artikelcode 

WHERE r.debiteurnr IN
(SELECT debiteurnr FROM orderinfo WHERE ordernr=1084)

Volgens mij was het zo.

IN i.p.v. = ... omdat je met een verzameling terug kan krijgen

Die wijziging is niet nodig omdat er maar 1 debiteurnr per ordernr is.

P_de_B schreef op vrijdag 29 april 2005 @ 14:31:
[...]


Dat klopt omdat je in je WHERE statement refereert naar een kolom in de OUTER gejoinde tabel.

code:

LEFT OUTER JOIN prijslijstperrelatie r ON p.artikelcode=r.artikelcode AND r.DebiteurNr = [nummer]

Dit moet volgens mij wel werken

Volgens mij is je idee wel goed, alleen ik denk dat er een syntax fout zit ergens. Ik krijg namelijk de volgende foutmelding: Join expression not supported.

[P_de_B]

ehm, je hebt wel op de plaats van [nummer] je subquery (of om te testen een geldig ordernummer) ingevuld?

edit: of je moet LEFT OUTER JOIN doorf LEFT JOIN vervangen misschien?

[ Voor 26% gewijzigd door P_de_B op 29-04-2005 15:03 ]

[Verwijderd]

Ja, het ordernummer is geldig (ik heb het zelfs geprobeerd door de subquery te vervangen door een debiteurnummer).

Of je nu LEFT OUTER JOIN of LEFT JOIN gebruikt, de foutmelding blijft hetzelfde.

[P_de_B]

Welke database gebruik je?

[Verwijderd]

MS Access 97

[MrScratch]

Gokje:

code:

SELECT p.artikelcode, p.merk, p.verpakkingshoeveelheid, 
p.verpakkingseenheid, p.inkoopprijs, p.verkoopprijs, DepPrices.verkoopprijs 
FROM prijslijst p 
LEFT JOIN (
    Select verkoopprijs, artikelcode
    from prijslijstperrelatie PPR
    where debiteurnr = (SELECT debiteurnr FROM orderinfo WHERE ordernr=1084)
    ) as DebPrices
ON p.artikelcode = Debprices.artikelcode

In de subquery selecteer je dus een lijst van aangepaste prijzen en codes van de betreffende debiteur. Deze leftjoin je op de totale prijslijst en dus krijg je alle prijzen met aangepaste prijzen van de debiteur indien deze bestaan

[bigbeng]

Dan is het inderdaad LEFT JOIN.
En aan je where clause kun je

code:

OR r.debiteurnr Is Null

toevoegen en dan zou ie gewoon alle artikelen laten zien, denk ik (ongetest).

[Verwijderd]

MrScratch schreef op vrijdag 29 april 2005 @ 15:29:
Gokje:

code:

SELECT p.artikelcode, p.merk, p.verpakkingshoeveelheid, 
p.verpakkingseenheid, p.inkoopprijs, p.verkoopprijs, DepPrices.verkoopprijs 
FROM prijslijst p 
LEFT JOIN (
    Select verkoopprijs, artikelcode
    from prijslijstperrelatie PPR
    where debiteurnr = (SELECT debiteurnr FROM orderinfo WHERE ordernr=1084)
    ) as DebPrices
ON p.artikelcode = Debprices.artikelcode

In de subquery selecteer je dus een lijst van aangepaste prijzen en codes van de betreffende debiteur. Deze leftjoin je op de totale prijslijst en dus krijg je alle prijzen met aangepaste prijzen van de debiteur indien deze bestaan

Dat lijkt me een goed idee. Ik heb hem ingevoerd, maar ik krijg de volgende foutmelding:
Syntax error in FROM clause.

bigbeng schreef op vrijdag 29 april 2005 @ 15:30:
Dan is het inderdaad LEFT JOIN.
En aan je where clause kun je

code:

OR r.debiteurnr Is Null

toevoegen en dan zou ie gewoon alle artikelen laten zien, denk ik (ongetest).

Dit werkt op zich wel, alleen nu krijg ik -wanneer een aangepaste prijs is ingevoerd- een rij zonder aangepaste prijs en exact dezelfde rij met aangepaste prijs te zien. Ik krijg dus dubbele resultaten.

[bigbeng]

Ja he, moet ik alles voor je oplossen

Probeer het eens met een SELECT DISTINCT ipv SELECT.

[Verwijderd]

bigbeng schreef op vrijdag 29 april 2005 @ 16:31:
Ja he, moet ik alles voor je oplossen

Probeer het eens met een SELECT DISTINCT ipv SELECT.

Haha...had ik zelf ook kunnen bedenken

Alleen het probleem is:
-nu wordt alleen een product weergegeven indien er geen aangepaste prijs is voor welk debiteurmr dan ook
-als er een aangepaste prijs is, wordt het product alleen weergegeven als het debiteurnr overeenstemt met het debiteurnr van de aangepaste prijs.

Het eerste punt is nu dus het probleem: hij moet ook producten weergeven als er een aangepaste prijs is en het debiteurnr niet overeenstemt

Ik hoop dat jullie het nog begrijpen. Ik gebruik nu de volgende query

code:

SELECT DISTINCT  p.artikelcode, 
       p.merk, 
       p.verpakkingshoeveelheid, 
       p.verpakkingseenheid, 
       p.inkoopprijs, 
       p.verkoopprijs, 
       r.verkoopprijs 
FROM (prijslijst AS p 
LEFT JOIN (productperrelatie r 
LEFT JOIN orderinfo o ON o.debiteurnr=r.relatienr)
ON p.artikelcode=r.artikelcode) WHERE r.relatienr=2 or r.relatienr is null

r.relatienr is hetzelfde als r.debiteurnr

[MrScratch]

Verwijderd schreef op vrijdag 29 april 2005 @ 16:28:
[...]


Dat lijkt me een goed idee. Ik heb hem ingevoerd, maar ik krijg de volgende foutmelding:
Syntax error in FROM clause.

Hmm, typisch. Probeer eens het stuk tussen de haakjes, dus na LEFT JOIN en voor DepPrices. Geeft die query alle artikelen die een aangepaste prijs hebben van de debiteur die je zoekt? Ik denk dat mijn query bijna perfect is, maar omdat ik zelf niet kan testen, zit er misschien een kleine verschrijving in.

Die andere query die je probeert is volgens mij geen goede manier om bij de oplossing te komen.

[MrScratch]

Goed ik heb het al gevonden. In mijn select stond 1 referentie naar dePprices ipv deBprices zoals ik de alias had genoemd. Ik heb een testsetje gemaakt en de query geeft precies terug wat jij wilt. Correcte code is dus:

code:

SELECT p.artikelcode, p.merk, p.verpakkingshoeveelheid, 
p.verpakkingseenheid, p.inkoopprijs, p.verkoopprijs, DebPrices.verkoopprijs 
FROM prijslijst p 
LEFT JOIN (
    Select verkoopprijs, artikelcode
    from prijslijstperrelatie PPR
    where debiteurnr = (SELECT debiteurnr FROM orderinfo WHERE ordernr=1084)
    ) as DebPrices
ON p.artikelcode = Debprices.artikelcode

[Verwijderd]

MrScratch schreef op vrijdag 29 april 2005 @ 17:06:
Goed ik heb het al gevonden. In mijn select stond 1 referentie naar dePprices ipv deBprices zoals ik de alias had genoemd. Ik heb een testsetje gemaakt en de query geeft precies terug wat jij wilt. Correcte code is dus:

code:

SELECT p.artikelcode, p.merk, p.verpakkingshoeveelheid, 
p.verpakkingseenheid, p.inkoopprijs, p.verkoopprijs, DebPrices.verkoopprijs 
FROM prijslijst p 
LEFT JOIN (
    Select verkoopprijs, artikelcode
    from prijslijstperrelatie PPR
    where debiteurnr = (SELECT debiteurnr FROM orderinfo WHERE ordernr=1084)
    ) as DebPrices
ON p.artikelcode = Debprices.artikelcode

Geweldig dat die query werkt! Dat had ik niet gezien (dePprices ipv deBprices). Echter, de correcte code zoals hierboven staat werkt nog steeds niet bij mijn database. Ik krijg nog steeds de error: Syntax errror in FROM clause. Ik heb daarom een testdatabase gemaakt. Zou je misschien wilen proberen of die query daarin werkt? De database is hier te downloaden: www25.brinkster.com/progmagic/testdatabase.mdb. Alvast heel erg bedankt!

[MrScratch]

Verwijderd schreef op vrijdag 29 april 2005 @ 21:30:
[...]
Geweldig dat die query werkt! Dat had ik niet gezien (dePprices ipv deBprices). Echter, de correcte code zoals hierboven staat werkt nog steeds niet bij mijn database. Ik krijg nog steeds de error: Syntax errror in FROM clause. Ik heb daarom een testdatabase gemaakt. Zou je misschien wilen proberen of die query daarin werkt? De database is hier te downloaden: www25.brinkster.com/progmagic/testdatabase.mdb. Alvast heel erg bedankt!

Helaas, als ik je database download, dan wil mijn Access 2003 hem niet openen. Overigens had ik vanmiddag SQL-server tot mijn beschikking, daarmee heb ik het getest. Misschien kan die meer dan Access op dit gebied, alhoewel het mij toch een beetje een basic querietje lijkt.

Ik zal eens een test-db'tje bouwen in Access. Kijken of het dan ook nog gaat.

[Creepy]

Sorry, maar dat is hier niet de bedoeling. Ik miste in dit topic al redelijk je eigen inzet, en met je laatste post laat je het echt overkomen als "hier je heb je alles, help me maar" en dat is zoals gezegd dus niet de bedoeling.

Lees aub P&W FAQ - De "quickstart" eens door zodat je ziet wat we van een topicstart verwachten.

[ Voor 3% gewijzigd door Creepy op 29-04-2005 22:47 ]