[MSSQL] Join 2 tabellen probleem

[Verwijderd]

Ik heb 2 tabellen

Tabel 1: producten
ProductID Omschrijving Prijs Voorraad
A Product A 10 100
B Product B 4 50
C Product C 9 30
enz...

Tabel 2: orders
Facnr Productid Aantal Verwerkt
1 A 2 Nee
1 C 1 Nee
2 A 1 Ja
3 A 5 Nee
4 B 9 Ja
enz..enz..

Nu wil ik in 1 query de volgende resultaten hebben:

Productid Voorraad Voorraad-Openstaand Prijs
A 100 (100-7) = 93 10
B 50 (50-0) = 50 4
C 30 (30-1) = 29 9

Hij moet dus in de bestellingen tabel kijken, en daar van het betreffende ProductId optellen hoeveel er gemarkeerd zijn met "Verwerkt = Nee". Dit moet hij aftrekken van de voorraad in de producten tabel.
Echter, als hij geen records in de bestellingen tabel kan vinden, moet het script wel dit product laten zien.

Ik ben uren met allerlei joins bezig geweest, maar kom er echt, (ook na veel zoeken op got/google) niet uit. Weet namelijk niet waar ik moet beginnen.
Heb wel wat dingen voor elkaar gekregen, maar dan laat hij de producten die hij niet in de bestellingen tabel kan vinden niet zien.

Wie kan me een zetje in de goede richting geven?

[curry684]

Dit lukt je alleen met subqueries, dit krijg je met een join ammenooitniet voor mekaar

[Verwijderd]

Kun je eens een voorbeeldje maken? Weet niet zo goed hoe subqueries werken. Doe het nu d.m.v. 1 hoofd query met daarna een subquery in een loop, maar dat duurt heel erg lang. Daarna sla ik alles in een temp tabel op, om deze vervolgens weer uit te gaan lezen. Moet sneller kunnen, maar hoe

[stp_4]

code:

SELECT
    Table1.id,
    Table1.prijs,
    ( SELECT Table1.voorraad - ( SELECT SUM (Table2.aantal) 
     FROM Table2 WHERE Table1.id = Table2.productID AND Table2.verwerk = 1)) AS totaal
FROM
    Table1

werkt prima, net getest

[ Voor 31% gewijzigd door stp_4 op 11-02-2005 13:08 ]

[curry684]

Kan trouwens ook met een join:

SQL:

SELECT p.ProductId, Voorraad, Voorraad - sum(Aantal), Prijs
FROM Producten AS p
JOIN Orders AS o ON p.ProductId = o.ProductId
WHERE Verwerkt = 0
GROUP BY p.ProductId, Voorraad, Prijs;

Werkt prima, net getest

Doe het nu d.m.v. 1 hoofd query met daarna een subquery in een loop, maar dat duurt heel erg lang.

Dat is geen subquery, een subquery is zoals je bij stp_4 ziet onderdeel van de query zelf en niet iets wat je los uitvoert (en dus stervenstraag is).

Volgens Query Analyzer zijn beide versies overigens exact even snel, zelfs al hebben ze een ander execution plan stp_4 genereert overigens wel een superfluous row met een NULL erin

[ Voor 16% gewijzigd door curry684 op 11-02-2005 13:10 ]

[P_de_B]

curry684 schreef op vrijdag 11 februari 2005 @ 12:46:
Dit lukt je alleen met subqueries, dit krijg je met een join ammenooitniet voor mekaar

edit: ok je was er dus al achter

edit2: je moet wel een left outer join doen met de orders tabel omdat records waarvan geen bestellingen zijn wel in de resultset moeten komen

curry684 schreef op vrijdag 11 februari 2005 @ 13:19:
[...]

Ik had de vraag niet helemaal goed gelezen

[ Voor 78% gewijzigd door P_de_B op 11-02-2005 13:19 ]

[curry684]

P_de_B schreef op vrijdag 11 februari 2005 @ 13:17:
[...]



edit: ok je was er dus al achter

Ik had de vraag niet helemaal goed gelezen

[Verwijderd]

Dat met dat joinen werkt goed, alleen niet in dit geval.

10 op voorraad, geen verkocht. In dat geval is de "vrije voorraad" NULL ipv 10.
Iemand daar nog een leuke truukje voor?

[lier]

ISNULL(Aantal, 0)

[Verwijderd]

Bedankt, het werkt!

[Verwijderd]

Toch gaat 't nog niet zo heel snel. Beide tabellen zijn immens groot.
Over welke kolommen kan ik voor deze query (niet de join, maar de bovenste) het beste welke index leggen?
Heb al wat indexen geprobeerd, maar daar gaat 't alleen trager door.

[P_de_B]

Verwijderd schreef op vrijdag 11 februari 2005 @ 15:23:
Toch gaat 't nog niet zo heel snel. Beide tabellen zijn immens groot.
Over welke kolommen kan ik voor deze query (niet de join, maar de bovenste) het beste welke index leggen?
Heb al wat indexen geprobeerd, maar daar gaat 't alleen trager door.

Ken je de index tuning wizard die in Query Analyzer zit? Deze kan je goed helpen bij dit probleem. Over het algemeen geldt dat kolommen waarop je joint en kolommen die in je WHERE clausule zitten in een index op moet nemen.

Gebruik ook eens 'Display exection plan' in query analyzer, hierin kun je goed de knelpunten indentificeren.

[ Voor 10% gewijzigd door P_de_B op 11-02-2005 15:27 ]

[curry684]

Wat is 'ProductId' uberhaupt voor veld gezien het feit dat er textuele data in staat?

* curry684 gaat vast preventief huilen voor een char(255) antwoord

[P_de_B]

curry684 schreef op vrijdag 11 februari 2005 @ 16:33:


* curry684 gaat vast preventief huilen voor een char(255) antwoord

Moet je voor de gein eens een exact database bekijken, ik moet voor elke query iets als het volgende doen:

WHERE LTRIM(RTRIM(artcode) = 123455

[Verwijderd]

curry684 schreef op vrijdag 11 februari 2005 @ 16:33:
Wat is 'ProductId' uberhaupt voor veld gezien het feit dat er textuele data in staat?

* curry684 gaat vast preventief huilen voor een char(255) antwoord

Het was puur ter illustratie. Het is netjes een INT

[Verwijderd]

Nu zit ik alleen nog met hetvolgende. Dit is de code:

code:

SELECT producten.productid, omschrijving, verkoopprijsexcleuro, verkoopprijsexclactieeuro, inkoopprijsexcleuro, actie, levertijd, actuelevoorraad, imageurl, prijsverandering, (SELECT CAST(actuelevoorraad AS int) - (SELECT ISNULL(SUM(CAST(Aantal AS int)),0) FROM bestelling WHERE producten.productid = bestelling.artikelid AND printed is null)) AS VrijeVoorraad
FROM producten
LEFT OUTER JOIN GroepKoppeling on producten.productid = GroepKoppeling.productid
where HgroepID = '47' AND Zichtbaar = '1'
order by omschrijving

Feit is dat er in deze database een veld "actie" aanwezig is, welke 1 en 0 kan zijn.
Als deze 1 is, moet de goede prijs uit de verkoopprijsexclACTIEeuro gehaald worden.
Is deze 0, dan moet de prijs uit de verkoopprijsexcleuro komen.

Output moet dus uiteindelijk 1 kolom met "GoedePrijs" zijn. Vervolgens moet er op deze "GoedePrijs" gesort worden. Leuke daarbij is ook nog dat de prijsvelden als VarChar ingevuld zijn

Ik ben nu 2 uur bezig, wie kan het sneller

[ Voor 4% gewijzigd door Verwijderd op 11-02-2005 18:39 ]

[jvdmeer]

Verwijderd schreef op vrijdag 11 februari 2005 @ 18:38:
[knip]

Feit is dat er in deze database een veld "actie" aanwezig is, welke 1 en 0 kan zijn.
Als deze 1 is, moet de goede prijs uit de verkoopprijsexclACTIEeuro gehaald worden.
Is deze 0, dan moet de prijs uit de verkoopprijsexcleuro komen.

Output moet dus uiteindelijk 1 kolom met "GoedePrijs" zijn. Vervolgens moet er op deze "GoedePrijs" gesort worden. Leuke daarbij is ook nog dat de prijsvelden als VarChar ingevuld zijn

Ik ben nu 2 uur bezig, wie kan het sneller

Niet zo heel moeilijk... Ga naar Query Analyzer, druk op F1. Zoek naar het woordje "CASE" in het gedeelte "Transact SQL" en je vindt enkele voorbeelden.

[Verwijderd]

Jullie zijn geweldig Het wordt prachtig....

Nog een heeeel klein probleempje:

Convert het VarChar veld "prijs" d.m.v. CAST(prijs as money) naar een money veld, zodat je hier op kunt sorteren. Probeem hierbij is dat het getal * 100 gedaan wordt. Waarschijnlijk omdat hij de "," als een "." ziet. Dat is goed op te lossen d.m.v. een "/100" achter de CAST te zetten. Dit gaat echter fout als er een "." in de prijs zit, bijvoorbeeld bij 1.000,45.

Hoe kan ik dit beter converteren? Of is er een manier dat hij de "." als een "," gaat zien, en de conversie wel goed gaat.

[Verwijderd]

Dit werkt opzich wel, maar is niet zo netjes natuurlijk:

code:

CAST(replace(replace(Prijs,'.',''),',','.') AS money)

Andere suggesties?

[stp_4]

Ik los dit soort dingen meestal op aan de presentatiekant van de data.