20. februar 2003 - 08:49Der er
7 kommentarer og 2 løsninger
Hjælp til SQL Query!
Hej
Jeg har følgende tabeller i en database
foodprices: -id (primærnøgle) -price -fromdate -foodid (fremmednøgle til table foods)
foods -id -name
foodorders -id -foodid -number -bookingid
bookings -id -med mere!
SELECT foodprices.foodprices_price AS FoodPrice, foods.food_name AS FoodName, foodorders.foodorder_pcs AS FoodPcs FROM foodorders INNER JOIN bookings ON foodorders.booking_id = bookings.booking_id INNER JOIN foods ON foodorders.food_id = foods.food_id INNER JOIN foodprices ON foodprices.food_id = foods.food_id WHERE (foodorders.booking_id = 9999) ORDER BY foodprices.foodprices_fromdate
Her er så problemet:
madvare kan have flere priser, der hver især dækker forskellige tidsrum. Når en vare får en ny pris indsættes en ny række i tabellen 'foodprices' som så er den nye foodorders skal bruge
Udfra et booking_id skal det være muligt at udtrække de vareliner der måtte være med antallet af bestilte vare samt den nyeste pris, dvs. hvor foodprices_fromdate er størst!
Derfor skal jeg kun have udtrukket een vareline pr bestilt vare og ikke som resultatet bliver af ovenstående SQL query hvor jeg får ligeså mange vareliner som der er priser for den enkelte vare.
Hvis jeg f.eks har to vare: vareA vareB
hvor vareA har priserne: 10 12 15 (nyeste pris) og vareB har prisen 25 og en bestilling har bestilt 5 stk af hver vare giver den viste SQL query resultatet:
Denne side indeholder artikler med forskellige perspektiver på Identity & Access Management i private og offentlige organisationer. Artiklerne behandler aktuelle IAM-emner og leveres af producenter, rådgivere og implementeringspartnere.
hvad med: SELECT max(foodprices.foodid), foodprices.foodprices_price AS FoodPrice, foods.food_name AS FoodName, foodorders.foodorder_pcs AS FoodPcs FROM foodorders INNER JOIN bookings ON foodorders.booking_id = bookings.booking_id INNER JOIN foods ON foodorders.food_id = foods.food_id INNER JOIN foodprices ON foodprices.food_id = foods.food_id WHERE (foodorders.booking_id = 9999) group by foodprices.foodprices_price AS FoodPrice, foods.food_name AS FoodName, foodorders.foodorder_pcs AS FoodPcs ORDER BY foodprices.foodprices_fromdate
Det er muligt at syntax ikke er 100% korrekt, men ideen er at vælge den 'foodprice' med det højeste id. Og med 'group by' så får du det pr. vare.
Ideen er nok god nok,men det fungerer ikke helt efter hensigten. Den udtrækker stadig et antal rækker svarende til varer x priser
Det virker som om at max() ikke rigtigt kommer igennem.
Jeg har prøvet med følgende hvor der udtrækkes efter højeste fromdate:
SELECT MAX(foodprices.foodprices_fromdate) AS FromDate, foodprices.foodprices_price AS FoodPrice, foods.food_name AS FoodName, foodorders.foodorder_pcs AS FoodPcs FROM foodorders INNER JOIN bookings ON foodorders.booking_id = bookings.booking_id INNER JOIN foods ON foodorders.food_id = foods.food_id INNER JOIN foodprices ON foodprices.food_id = foods.food_id WHERE (foodorders.booking_id = 9999) GROUP BY foodprices.foodprices_price, foods.food_name, foodorders.foodorder_pcs
Man kan f.eks. gøre således, hvilket dog desværre kun returnere den første vareline:
SELECT DISTINCT foods.food_name, foodprices.foodprices_price, foodorders.foodorder_pcs FROM foodprices INNER JOIN foods ON foodprices.food_id = foods.food_id INNER JOIN foodorders ON foodorders.food_id = foods.food_id INNER JOIN bookings ON bookings.booking_id = foodorders.booking_id WHERE (foodprices.foodprices_fromdate = (SELECT MAX(foodprices_fromdate) AS Expr1 FROM foodprices WHERE (food_id = SOME (SELECT foodorders.food_id FROM foodorders WHERE foodorders.booking_id = 71)))) AND (bookings.booking_id = 71)
SELECT max(foodprices.foodid), foodprices.foodprices_price AS FoodPrice, foods.food_name AS FoodName, foodorders.foodorder_pcs AS FoodPcs FROM foodorders INNER JOIN bookings ON foodorders.booking_id = bookings.booking_id INNER JOIN foods ON foodorders.food_id = foods.food_id INNER JOIN foodprices ON foodprices.food_id = foods.food_id WHERE (foodorders.booking_id = 9999) group by foodprices.foodprices_price, foods.food_name, foodorders.foodorder_pcs ORDER BY foodprices.foodprices_fromdate burde gøre jobbet - bemærk jeg har fjernet 'AS ....' under 'Group by'!
Hvis du vil hænge dig i petitesser - så gør det ;-)
..men så burde max(foodprices.fromdate) også virke!
I øvrigt så vil max(foodprices.fromdate) og max(foodprices.id) i dit tilfælde give det samme med følgende forudsætninger: 1. Man bruger autoincrement på foodprices.id 2. Man ikke opretter en foodprices der har en ældre fromdate end en af de foregående foodprices med samme foodid.
Og med din datamodel giver det ikke mening at oprette en foodprices der har en ældre fromdate end en af de foregående foodprices med samme foodid. Hvis du vil have en sådan fleksibilitet så bør du angive en periode på dine foodprices og sikre at der ikke er overlap på dine perioder!
Så fandt jeg ud af det, med lidt hjælp fra cooljay2000 så vi deler poitene makker! For ikke at få flere rækker returneret, skulle jeg bruge limit X,X hvilket iøvrigt ikke eksistere i MS SQL, så i stedet brugte jeg SET ROWCOUNT.
Det gav følgende resultat der virker !!
SELECT foods.food_id AS FoodID, foods.food_name AS FoodName, MAX(foodprices.foodprices_fromdate) AS FromDate, foods.food_available AS FoodAvailable, foodprices.foodprices_price AS FoodPrice FROM foods INNER JOIN foodrelationship ON foods.food_id = foodrelationship.food_id INNER JOIN foodprices ON foodprices.food_id = foods.food_id WHERE (foods.food_id = 9999) GROUP BY foods.food_id, foods.food_name, foodprices.foodprices_price, foods.food_available ORDER BY foodprices.foodprices_price DESC SET ROWCOUNT 1
Thats it, men så blev jeg også meget klogere på brugen af Group BY og SET ROWCOUNT :O)
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
