Group By of Join
ik zit met twee tabellen. "Articles" en "pricetable".
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS `articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`description` text COLLATE utf8_unicode_ci NOT NULL,
`categorie` int(2) NOT NULL,
`picture` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `categorie` (`categorie`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`description` text COLLATE utf8_unicode_ci NOT NULL,
`categorie` int(2) NOT NULL,
`picture` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `categorie` (`categorie`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS `pricetable` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`ArticleId` int(2) NOT NULL,
`Date` date NOT NULL,
`price` double(10,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `ArticleId` (`ArticleId`),
KEY `ArticleId_2` (`ArticleId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;
`id` int(2) NOT NULL AUTO_INCREMENT,
`ArticleId` int(2) NOT NULL,
`Date` date NOT NULL,
`price` double(10,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `ArticleId` (`ArticleId`),
KEY `ArticleId_2` (`ArticleId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;
Nu wil ik alle articles ophalen behalve diegene met hetzelfde articleId daar wil ik alleen de laatste van ophalen want dat is de laatste prijs.
Ik heb al geprobeert met group by maar dat was toch niet de geweldigste oplossing dus ik denkt dat ik misschien een andere join-type moet gebruiken?
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT p.id,a.name,a.description,a.picture,p.price
FROM pricetable AS p
INNER JOIN articles as a on p.articleId = a.id
WHERE categorie = :categorie
GROUP BY a.id
Order BY p.articleId DESC
FROM pricetable AS p
INNER JOIN articles as a on p.articleId = a.id
WHERE categorie = :categorie
GROUP BY a.id
Order BY p.articleId DESC
Kan een SQL-goeroe mij even helpen? :-)
Bedankt!
Gewijzigd op 05/04/2013 16:11:48 door Jasper DS
Zou je alsjeblieft die query wat beter op kunnen schrijven. Zo op 1 regel is het gewoon niet te lezen. Je schrijft een heel php script toch ook niet op 1 regel?
Dan een inhoudelijke vraag. Wat is de relatie tussen de gegevens in de twee tabellen? Wat staat in de 'articles' tabel en wat staat er in de 'pricetable'. Je zegt namelijk dat je alle articles wilt ophalen, behalve die met hetzelfde id. Maar daar denk ik dat je het verkeerd zet, want ik neem aan dat je niet twee articles hebt met hetzelfde id, dan gaat het om de prijzen, of niet?
De reden waarom ik twee tabellen heb is omdat de prijs kan veranderen en ik een "history" wil. Als iemand product x koopt aan €250 dan staat er op zijn factuur het id van de priceTable waar de prijs €250 is en het articleID het id van product x is.
Je hebt nu één prijsmodel: een prijs per product die kan veranderen in de tijd. Maar er zijn véél meer prijsmodellen mogelijk. Dat kan zelfs tot het niveau van een individuele prijs per klant of type klant.
Gewijzigd op 05/04/2013 16:29:15 door Ward van der Put
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT p.id, a.name, a.description, a.picture, p.price
FROM articles a
INNER JOIN (
SELECT articleid, MAX(date) AS last_date
FROM pricetable
GROUP BY articleid
) m ON a.articleid = m.articleid
INNER JOIN pricetable p ON (
p.articleid = m.articleid
AND p.date = m.last_date
)
WHERE a.categorie = :categorie
ORDER BY a.articleid;
FROM articles a
INNER JOIN (
SELECT articleid, MAX(date) AS last_date
FROM pricetable
GROUP BY articleid
) m ON a.articleid = m.articleid
INNER JOIN pricetable p ON (
p.articleid = m.articleid
AND p.date = m.last_date
)
WHERE a.categorie = :categorie
ORDER BY a.articleid;
Toevoeging op 05/04/2013 16:56:50:
De opmerking van Ward zou je vraag en query overigens niet veranderen als je de laatste prijs wilt weten. Misschien zou je het alleen uit een andere tabel halen.
Als één product altijd één vaste prijs heeft, mis je de flexibiliteit die een online marketeer nodig heeft. Kijk bijvoorbeeld eens naar de "price rules" van Magento. Die zijn niet altijd even duidelijk, maar je kunt er van alles mee. Tweede voor de helft, 2 + 1 gratis, bij aankoop van 12 flessen een kurkentrekker cadeau, noem maar op.
Volgens sommige schattingen wordt ongeveer de helft van de online aankopen al gestuurd door een of andere vorm van individuele prijsstelling op gebruikersniveau. Wie met een supersnelle internetverbinding op een luxe MacBook Pro een vliegticket boekt, betaalt meer...
WAAROM hij die laatste prijs wil hebben is een andere vraag, maar ik geloof niet dat dat ter discussie stond in dit topic (wat niet wil zeggen dat het geen interessant onderwerp is overigens).
En mijn principe is dan altijd: sla de prijs van een product op wanneer het verkocht wordt.
Met andere woorden in de order_products tabel
@Ward, de website die ik maak is bij wijze van oefening. Of het volledig economisch correct is zal ik over nadenken moest ik ooit een echte webshop maken. Toch bedankt!
Ward van der Put op 05/04/2013 17:11:46:
Volgens sommige schattingen wordt ongeveer de helft van de online aankopen al gestuurd door een of andere vorm van individuele prijsstelling op gebruikersniveau. Wie met een supersnelle internetverbinding op een luxe MacBook Pro een vliegticket boekt, betaalt meer...
Heb je hier ook aantoonbare bewijzen van? Het lijkt mij sterk dat persoon A en persoon B een verschilende prijs betalen voor een vliegticket, omdat persoon A achter een "simpele" PC met matige internetverbinding de ticket boekt, terwijl persoon B gebruikmaakt van een high-tech laptop met een supersnelle verbinding. Ik kan me sowieso niet voorstellen dat dit in Nederland gebeurt. Ik denk dat dat bij wet verboden is.
Waar Amazon prijzen per individu aanpast, doet bijvoorbeeld Dell dat ruimer op het niveau van doelgroepen: “One day recently, the Dell Latitude L400 ultralight laptop was listed at $2,307 on the company's Web page catering to small businesses. On the Web page for sales to health-care companies, the same machine was listed at $2,228, or 3% less. For state and local governments, it was priced at $2,072.04, or 10% less than the price for small businesses.”
Hoewel het dus oud nieuws is, ontstond er in juni 2012 opnieuw ophef over deze techniek door een artikel in The Economist. Hierin vind je ook het concrete advies: “Surf on a PC, not an Apple.”
Je mag met iedereen een individuele koopovereenkomst sluiten. En de overeengekomen prijs is daarvan slechts een onderdeel, dus ik denk dat deze vormen van "individual pricing" niet per se verboden zijn. Op een grote aanschaf (van wasmachine tot auto) probeer ik altijd af te dingen. En bij de koop van een huis betaalt bijna niemand in Nederland de vraagprijs. Een individuele prijs bedingen is dus niet ongebruikelijk.
Dus, stel een doorsnee man loopt een electronica winkel binnen... er gebeurt niks. De man loopt de winkel uit. Vervolgens stopt er een statige man met een dikke Jaguar voor de deur. Snel wordt razendsnel ieder prijskaartje van ieder product omgedraaid door het personeel zodat er een hogere prijs op staat. Zou dat mogen?
Het voelt als Nederlanders die in het buitenland op straat worden aangesproken door een louche verkoper... "sir, special price for you!!"
http://www.spitsnieuws.nl/archives/binnenland/2013/04/efteling-geeft-duitsers-korting