Hej!

Jag har en enkel fråga tror jag!

Jag har en tabell product som ser ut enligt:

product
--------
productidid
name
description
...


I systemet finns också en tabell variant enligt:

variant
--------
variantid
productid
price
pricerecommended
....

Det kan alltså finnas flera varianter på en produkt...

Jag vill retunera en lista med produkter med dessa fält

productid
name
description
price
pricerecommended

...där price och pricerecommended kommer från den post i varianttabellen knuten till den specifika artikeln med lägst price!

/hur gör man detta smidigt i en sql sats?

/tack!
Stefan

Du skriver inte vilken databas, men om du vill ha det lägsta priset och lägsta rekommenderade priset kan du köra något i stil med detta. Notera att jag skrivit lite MySQL specifik syntax, så har du en annan databas får du säkert lägga till "AS xxx" för price, pricerecommended, p och v.
<code>
SELECT p.productid, p.name, p.description, MIN(v.price) price, MIN(v.pricerecommended) pricerecommended FROM product p
JOIN variant v USING (productid)
GROUP BY p.productid
</code>
Vill du däremot koppla den raden från variant tabellen till product tabellen där price är lägst för en produkt och ta både priset och det rekommenderade priset från samma rad fungerar inte ovanstående för det.

<code>
select p.productid,
p.name,
p.description,
v.price,
v.pricerecommended
from product p
join variant v on p.productid = v.productid
where price in
(select min(price)
from variant
where variant.productid = p.productid)
</code>

hej lars!

tack tack denna funkar enligt min beskrivning men jag kom ju självklart på en grej till!

en produkt kan t.ex. ha varianter med:

price pricerecommended
495 1295
495 1195
495 1495

och din sql kommer då returnera alla dessa rader...

jag vill låsa den till kombinationen av price och pricerecommended som ihop är minsta dvs i detta fallet den mellersta raden med price 495 och pricerecommended 1195.

ett annat fall kan vara:
price pricerecommended
395 1495
495 1195
495 1295

och då vill jag retunera första raden med price 395 och pricerecommended 1495, price är alltså "styrande" även om pricerecommended i detta fall inte är det lägsta...

/tack igen!
/stefan

hej thomas!

tack för ditt svar. jag jobbar mot mysql.

precis som du skriver sist vill jag ta det från samma rad och då funkar inte riktigt detta. kolla mitt svar till lars så fattar du nog!

tack för hjälpen!
/mvh stefan

<code>
select p.productid,
p.name,
p.description,
v.price,
min(v.pricerecommended) as pricerecommended
from product p
join variant v on p.productid = v.productid
where price in
(select min(price)
from variant
where variant.productid = p.productid)
group by p.productid,
p.name,
p.description,
v.price
</code>

tjena lars!

detta verkar ju funka finfint!

kan du förklara lite hur det funkar?

behövs verkligen min(v.pricerecommended) as pricerecommended först i select?

behövs verkligen p.name, p.description och v.price i group by?

/tacktack
Stefan

1. product och variant tabellerna sammanfogas med productid.
2. WHERE delen säger att enbart ta de rader som har det lägsta priset för den produkten.
3. MIN i SELECT delen säger att minsta möjliga värde för pricerecommended ska returneras. Alla rader för en produkt kommer att ha sama price.
4. GROUP BY grupperar produkterna till enstaka rader, MIN fungerar inte annars.
5. Och nej, du behöver inte mer än p.productid i GROUP BY då denna är unik i sig.

Tänk dock på att denna SQL sats kör en subquery för varje rad, vilket med många rader kan bli väldigt prestandakrävande (uppgå till tusentals interna frågor i databasen).

Testade göra det samma med en extra JOIN, borde vara bättre prestanda på större datamängder.
<code>
SELECT p.productid, p.name, p.description, v.price, MIN(v2.pricerecommended) pricerecommended FROM product p
JOIN
(
SELECT productid, MIN(price) price FROM variant
GROUP BY productid
) v ON v.productid = p.productid
JOIN variant v2 ON v2.productid = p.productid AND v2.price = v.price
GROUP BY p.productid
</code>

utmärkt förklaring :)

jag kör mot mysql 5 och din andra variant vill inte fungera...

jag antar att det är delen:

JOIN
(
SELECT productid, MIN(price) price FROM variant
GROUP BY productid
) v ON v.productid = p.productid

som ställer till det? eller missar jag nåt?

vad gäller prestandan tror jag inte det kommer bli nåt problem... dels har vi i nuläget endast ca 50 produkter med 200 varianter. Dessutom är produkterna uppdelade i kategorier så denna sql sats joinas med en länktabell som endast plockar ut de produkter i den kategori som listas...

/stefan

Svårt att säga utan att få veta felmeddelandet MySQL ger, men troligtvis kan det vara så att även subqueryn måste ha ett alias för att MySQL ska kunna tyda frågan.
Testa med nedanstående.
<code>
SELECT p.productid, p.name, p.description, v.price, MIN(v2.pricerecommended) pricerecommended FROM product p
JOIN
(
SELECT var.productid, MIN(var.price) price FROM variant var
GROUP BY var.productid
) v ON v.productid = p.productid
JOIN variant v2 ON v2.productid = p.productid AND v2.price = v.price
GROUP BY p.productid
</code>
Det är bra att programmera så optimerat man kan, även om inte det för tillfället kräver det (om det inte förstås tar mycket längre tid).

Hej Thomas,

Felmeddelandet från mysql var: unknown column 'v.Price' in 'field list'

Det var jag som strulat till det, i subqueryn hade jag missat att lägga till aliaset price för Min(var.price)

Tack tack!

Optimera är alltid bra. Mycket sant.

/s