MySQL Query Laagste prijs en op voorraad

[Verwijderd]

ik heb de volgende MySQL tabel:

tabel: tbl_prodlev
`id` int(11) NOT NULL auto_increment,
`prod_id` int(11) NOT NULL,
`leverancier` int(11) NOT NULL,
`aantal` int(11) NOT NULL,
`prijs` int(11) NOT NULL,
PRIMARY KEY (`id`)

Nu is het zo dat er bijvoorbeeld 3 keer product 100 in deze tabel kan voorkomen met andere leveranciers. Nu zou ik dus de ID moeten hebben van het product dat het goedkoopst is EN ook op voorraad is.

Zelf was ik al bezig geweest met:

SQL:

SELECT t1.* 
FROM tbl_prodlev t1 
INNER JOIN tbl_prodlev t2 ON  t1.prod_id= t2.prod_id
GROUP By t1.prod_id, t1.prod_id
HAVING min(t1.prijs) = Min(t2.prijs) AND t1.aantal > -1

Alleen werkt deze query niet helemaal goed, ik krijg altijd het eerste product terug: dus als er 3x product X in de tabel staat krijg ik de 1e terug.

Iemand enig idee hoe dit op te lossen is?

[gorgi_19]

SQL:

SELECT TOP 1 * FROM Tabelnaam WHERE Aantal > 0 WHERE ProductId = JeProductNummer ORDER BY prijs DESC

En die TOP moet je dan zelf omschrijven naar de MySQL variant met Limit

[ Voor 10% gewijzigd door gorgi_19 op 20-11-2007 13:19 ]

[Verwijderd]

gorgi_19 schreef op dinsdag 20 november 2007 @ 13:18:

SQL:

SELECT TOP 1 * FROM Tabelnaam WHERE Aantal > 0 ORDER BY prijs DESC

En die TOP moet je dan zelf omschrijven naar de MySQL variant met Limit

Dit bedoel ik niet precies, ik wil dus alleen records terug krijgen met de laagste prijs en op voorraad, niet 1 row, maar meerdere rows (produkten).

[Dido]

Wat probeer je precies te doen met je inner join? Je bereikt er op deze manier namelijk niet veel mee

Je group by op 2 velden bij een select * is sowieso een doodzonde.

[Greyfox]

Ik denk dat je met subqueries moet werken

[rrrandy]

Verwijderd schreef op dinsdag 20 november 2007 @ 13:16:
ik heb de volgende MySQL tabel:

tabel: tbl_prodlev
`id` int(11) NOT NULL auto_increment,
`prod_id` int(11) NOT NULL,
`leverancier` int(11) NOT NULL,
`aantal` int(11) NOT NULL,
`prijs` int(11) NOT NULL,
PRIMARY KEY (`id`)

Nu is het zo dat er bijvoorbeeld 3 keer product 100 in deze tabel kan voorkomen met andere leveranciers. Nu zou ik dus de ID moeten hebben van het product dat het goedkoopst is EN ook op voorraad is.

Zelf was ik al bezig geweest met:

SQL:

SELECT t1.* 
FROM tbl_prodlev t1 
INNER JOIN tbl_prodlev t2 ON  t1.prod_id= t2.prod_id
GROUP By t1.prod_id, t1.prod_id
HAVING min(t1.prijs) = Min(t2.prijs) AND t1.aantal > -1

Alleen werkt deze query niet helemaal goed, ik krijg altijd het eerste product terug: dus als er 3x product X in de tabel staat krijg ik de 1e terug.

Iemand enig idee hoe dit op te lossen is?

toon volledige bericht

Nogal wiedes, de HAVING-clause beperkt je hele query tot 1 record per group...

[Verwijderd]

1 record per group is toch wat ik moet hebben? van elke prod_id moet ik 1 resultaat hebben namelijk de goedkoopste en op vooraad....

[Dido]

Iets als dit?

SQL:

SELECT *
  FROM tabel a
 WHERE a.prijs = (SELECT MIN(b.prijs) 
                    FROM tabel b 
                   WHERE b.prod_id = a.prod_id)
   AND a.aantal > -1;

Zoiets kwam gisteren ook al langs dacht ik.
Maarre, ik ben wel benieuwd naar het antwoord op mijn eerdere vraag

[ Voor 27% gewijzigd door Dido op 20-11-2007 13:42 ]

[gorgi_19]

Dido schreef op dinsdag 20 november 2007 @ 13:40:
Iets als dit?

SQL:

SELECT *
  FROM tabel a
 WHERE a.prijs = (SELECT MIN(b.prijs) 
                    FROM tabel b 
                   WHERE b.prod_id = a.prod_id)
   AND a.aantal > -1;

Zoiets kwam gisteren ook al langs dacht ik.
Maarre, ik ben wel benieuwd naar het antwoord op mijn eerdere vraag

Dat zal niet goed gaan als 2 verschillende producten dezelfde prijs hebben, maar niet allebei de laagste in hun categorie zijn.

[Verwijderd]

Thnx, de juiste query zou zijn:

SQL:

SELECT * FROM test_lev a WHERE a.leverancier = (
    SELECT b.leverancier FROM test_lev b WHERE 
    b.aantal !=0 AND b.prod_id=a.prod_id ORDER BY b.prijs ASC LIMIT 1
)

[ Voor 3% gewijzigd door Verwijderd op 20-11-2007 13:52 ]

[gorgi_19]

* gorgi_19 gokt van niet.
Een leverancier is niet per definitie in alle categorieen de laagste

[Dido]

Die query doe misschien wat je wil maar waarom ga je in hemelsnaam aan het klooien met die leverancier
Je bent enorm omslachtig bezig, echt waar. Ik betwijfel of je door hebt wat je doet, vandaar mijn eerdere vraag, waar ik nog steeds heel graag antwoord op zou willen hebben.

[Verwijderd]

Dido schreef op dinsdag 20 november 2007 @ 13:53:
Die query doe misschien wat je wil maar waarom ga je in hemelsnaam aan het klooien met die leverancier
Je bent enorm omslachtig bezig, echt waar. Ik betwijfel of je door hebt wat je doet, vandaar mijn eerdere vraag, waar ik nog steeds heel graag antwoord op zou willen hebben.

Ik denk dat je niet precies begrijpt wat ik precies wil:

ik heb dus tabel met de volgende velden:

• ID
• PROD_ID
• LEVERANCIER_ID
• AANTAL
• PRIJS

In deze tabel heb ik de volgende data staan:
ID: 1
Produkt = 1
Leverancier = 1
Aantal = 123
Prijs = 500

ID: 2
Produkt = 1
Leverancier = 2
Aantal = 65
Prijs = 400

ID: 3
Produkt = 2
Leverancier = 1
Aantal = 1
Prijs = 300

ID: 4
Produkt = 2
Leverancier = 2
Aantal = 0
Prijs = 100

ID: 5
Produkt = 3
Leverancier = 1
Aantal = 5
Prijs = 300

ID: 6
Produkt = 3
Leverancier = 2
Aantal = 2
Prijs = 300

Hetgene wat ik nu wil is van elk produkt de leverancier die het goedkoopste is + het product op vooraad heeft.

dus de record met id 2, 3, 5 of 6 (kosten evenveel)

[ Voor 3% gewijzigd door Verwijderd op 20-11-2007 16:09 ]

[Dido]

En welke records krijg je uit mijn query?

En een laatste keer dan: geef nou eens antwoord op mijn eerste vraag?
Ik heb echt het vermoeden dat je iets anders denkt te bereiken met je joins dan waar ze voor zijn.

gorgi_19 schreef op dinsdag 20 november 2007 @ 13:46:
Dat zal niet goed gaan als 2 verschillende producten dezelfde prijs hebben, maar niet allebei de laagste in hun categorie zijn.

Deze volg ik niet helemaal. Ik ben in het verhaal van de TS geen "categorie" tegengekomen.
Volgens mij levert mijn subquery de laagste prijs voor ieder artikel (prod_id), en krijg je dus het record terug voor dat artikel met die laagste prijs. Maar misschien heb ik inderdaad een "categorie" gemist?

[ Voor 89% gewijzigd door Dido op 20-11-2007 16:11 ]

[Verwijderd]

Dido schreef op dinsdag 20 november 2007 @ 16:06:
En welke records krijg je uit mijn query?

Record 2, 4, 5 en 6

[justmental]

Dido schreef op dinsdag 20 november 2007 @ 16:06:
En welke records krijg je uit mijn query?

De goedkoopste leverancier per produkt, ALS die voorraad > -1 heeft.
Volgens mij wil hij de goedkoopste die hem wel op voorraad heeft.

dus iets als:

code:

select a.Produkt, a.Leverancier, a.Aantal, a.Prijs
from tabel a
where a.Prijs = (select min(b.Prijs) from tabel b where b.Produkt=a.Produkt and b.Aantal > 0)

Gelijke prijzen even daargelaten.

[Dido]

Verwijderd schreef op dinsdag 20 november 2007 @ 16:09:
Record 2, 4, 5 en 6

Ja lieverd. Jij vertaalde zelf "op voorraad" naar "aantal > -1". Als ik dat dan vervolgens ook doe moet je me dat niet kwalijk nemen. Als je wilt weten waar artikelaantal > 0, dan moet je dat invullen

[Verwijderd]

Dido schreef op dinsdag 20 november 2007 @ 16:13:
[...]

Ja lieverd. Jij vertaalde zelf "op voorraad" naar "aantal > -1". Als ik dat dan vervolgens ook doe moet je me dat niet kwalijk nemen. Als je wilt weten waar artikelaantal > 0, dan moet je dat invullen

haha, nu je het zegt stom van me

[bigbeng]

Dan nog zou er een record teveel wegvallen. Hij wil de laagst mogelijke prijs van een produkt dat op voorraad is bij een leverancier. Als je jouw query nu zou loslaten met >0 ipv >-1 dan valt ook nummer 4 eruit.

edit:
eerst lezen, dan denken en dan pas schrijven

[ Voor 13% gewijzigd door bigbeng op 20-11-2007 16:18 ]

[gorgi_19]

Dido schreef op dinsdag 20 november 2007 @ 16:06:
En welke records krijg je uit mijn query?

En een laatste keer dan: geef nou eens antwoord op mijn eerste vraag?
Ik heb echt het vermoeden dat je iets anders denkt te bereiken met je joins dan waar ze voor zijn.

[...]

Deze volg ik niet helemaal. Ik ben in het verhaal van de TS geen "categorie" tegengekomen.
Volgens mij levert mijn subquery de laagste prijs voor ieder artikel (prod_id), en krijg je dus het record terug voor dat artikel met die laagste prijs. Maar misschien heb ik inderdaad een "categorie" gemist?

Erhm.. leverancier bedoel ik.

Stel:

Product A, leverancier Y, Prijs 2,30
Product A, leverancier Z, Prijs 2,40

Product B, leverancier L, Prijs 2,20
Product B, leverancier M, Prijs 2,30
Product B, leverancier N, Prijs 2,40

Ook de prijs van leverancier M zal getoond dan worden

[Dido]

Nu heeft justmental wel een punt: even een haakje verplaatsen en b.aantal ipv a.aantal afvragen is logischerder:

SQL:

SELECT *
  FROM tabel a
 WHERE a.prijs = (SELECT MIN(b.prijs) 
                    FROM tabel b 
                   WHERE b.prod_id = a.prod_id
                     AND b.aantal > 0);

[Dido]

gorgi_19 schreef op dinsdag 20 november 2007 @ 16:18:
[...]

Erhm.. leverancier bedoel ik.

Stel:

Product A, leverancier Y, Prijs 2,30
Product A, leverancier Z, Prijs 2,40

Product B, leverancier L, Prijs 2,20
Product B, leverancier M, Prijs 2,30
Product B, leverancier N, Prijs 2,40

Ook de prijs van leverancier M zal getoond dan worden

Waarom? Leverancier M komst alleen voor bij product B, aangezien a.product = b.product komt hij nooit naar boven uit:

SQL:

select min(prijs) from tabel where prod_id = "A"

of

SQL:

select min(prijs) from tabel where prod_id = "B"

En dat zijn de subqueries die uitgevoerd worden voor de producten a en b.

[Verwijderd]

SQL:

SELECT * FROM test_lev a WHERE a.leverancier = ( 
    SELECT b.leverancier FROM test_lev b WHERE  
    b.aantal !=0 AND b.prod_id=a.prod_id ORDER BY b.prijs ASC LIMIT 1 
)

[Mental]

ben ik nou zo ongelovelijk simpel of is dit gewoon de makkelijkste manier:

SQL:

SELECT prod_id, prijs
FROM tbl_prodlev
WHERE aantal > 0
ORDER BY prijs ASC
LIMIT 0,1

edit: moeten natuurlijk wel de goedkoopste hebben ipv de duurste
edit2: juist, meerdere records.. lezen is ook een kunst

[ Voor 26% gewijzigd door Mental op 20-11-2007 16:27 ]

[bigbeng]

L4m0r schreef op dinsdag 20 november 2007 @ 16:22:
ben ik nou zo ongelovelijk simpel of is dit gewoon de makkelijkste manier:

SQL:

SELECT prod_id, prijs
FROM tbl_prodlev
WHERE aantal > 0
ORDER BY prijs ASC
LIMIT 0,1

edit: moeten natuurlijk wel de goedkoopste hebben ipv de duurste

Ja en nee
Volgens mij krijg jij hier toch echt maar een product uit, namelijk het allergoedkoopste product. Hij wil van elk product de goedkoopste leverancier hebben die het betreffende product op voorraad heeft.

edit:
snel je post editten he

[ Voor 3% gewijzigd door bigbeng op 20-11-2007 16:28 ]

[Mental]

Idd, ik las alleen de eerste post toen ik antwoord gaf, daarna pas rest gelezen

maarre, zoiets dan maar:

SQL:

SELECT prod_id, leverancier, aantal, prijs
FROM products
WHERE aantal >0
GROUP BY prod_id
ORDER BY prijs ASC

(werkend getest)


edit: hier is dus absoluut geen subquery voor nodig, gewoon even nadenken en je query goed opbouwen.. greatness comes trough simplicity.

[ Voor 24% gewijzigd door Mental op 20-11-2007 16:39 ]

[Dido]

Verwijderd schreef op dinsdag 20 november 2007 @ 16:22:

SQL:

SELECT * FROM test_lev a WHERE a.leverancier = ( 
    SELECT b.leverancier FROM test_lev b WHERE  
    b.aantal !=0 AND b.prod_id=a.prod_id ORDER BY b.prijs ASC LIMIT 1 
)

Goed kans dat die het ook doet, inderdaad. Maar ik vind het zelf spuug, maar dan ook spuuglelijk om te sorteren op een kolom die je niet selecteert. Zo van "geef me alle voornamen, maar sorteer ze op telefoonnummer".

En de enige reden dat je dat doet is dat je per se wilt linken op leverancier in plaats van op laagste prijs.

Ja, je krijgt meerdere resultaten terug voor een artikel als er meerdere laagste prijzen zijn. Als je dat niet wilt moet je je afvragen welke je dan wilt hebben - alleen het criterium "laagste prijs" is dan niet genoeg.

Jouw query zal waarschijnlijk willekeurig 1 record teruggeven als er meerdere laagste prijzen zijn, maar is dat wel wat je wilt?

• Waarom kies je leverancier X?
• Laagste prijs
• Maar leverancier Y heeft dezelfde prijs, en levert sneller
• Ja, eh, mijn query doet dat nou toevallig zo

Je kunt je sortering niet eens controleren, want je selecteert je sorteerkolom niet

[Dido]

L4m0r schreef op dinsdag 20 november 2007 @ 16:37:
Idd, ik las alleen de eerste post toen ik antwoord gaf, daarna pas rest gelezen

maarre, zoiets dan maar:

SQL:

SELECT prod_id, leverancier, aantal, prijs
FROM products
WHERE aantal >0
GROUP BY prod_id
ORDER BY prijs ASC

(werkend getest)

Werkend, met de randomizer die MySQL heet zeker. Welke leverancier, aantal en prijs krijg je bij iedere prod_id waar je op grouped? Dat weet je niet. Sterker nog, de specs van MySQL garanderen dat je dat niet weet. Dus ik weet niet wat er werkt?

[Mental]

*schrap*

je hebt een punt, het is ook een hele vieze query ^^
Maargoed, dit ruikt naar huiswerk dus leef je uit wiseman.

[ Voor 80% gewijzigd door Mental op 20-11-2007 16:43 ]

[Dido]

Gauw editten, he?

[Mental]

haha, ssst