Left join samt sum

Hej igen...

Jeg har arbejdet lidt videre på den, men den giver nogle problemer for nu ligges alle sumtallene sammen:

SELECT b.id AS basket_id, b.x AS basket_x, b.y AS basket_y, b.price AS basket_price, b.amount AS basket_amount, b.text AS basket_text, p.id AS product_id, p.price_number AS product_price_number, p.name AS product_name, p.description AS product_description, p.memo AS product_memo, p.picture AS product_picture, c.id AS category_id, c.name AS category_name, c.description AS category_description, SUM( ba.price ) AS basket_addons_sum, SUM( bc.price ) AS basket_colours_sum, SUM( bg.price ) AS basket_glas_sum
FROM (
(
(
(
(
basket_added b
LEFT JOIN products p ON b.product = p.id
)
LEFT JOIN products_categories c ON p.category = c.id
)
LEFT JOIN basket_addons ba ON b.id = ba.basket_id
)
LEFT JOIN basket_colours bc ON b.id = bc.basket_id
)
LEFT JOIN basket_glas bg ON b.id = bg.basket_id
)
WHERE b.session_id =9612
GROUP BY b.id;

Korrektion: Det er selvfølgelig ikke sumtallene den ligger sammen på tværs af hinanden, men derimod bliver der summeret det antal gange hver linie vises.

Du kan tilføje outer eller inner join til en inline forespørgsel, f.eks.

LEFT JOIN (
  SELECT basket_id, SUM(price) AS prissum
  FROM basket_addons
  GROUP BY basket_id) AS bao ON bao.basket_id = b.id

og så tilføje feltet bao.prissum til din SELECT øverst

Hej kjulius

Det er jo helt perfekt, den virker bare... Har været søvnløs hele natten over denne query og havde lidt i tankerne at der nok skulle en subselect på, men kunne ikke lige overskue den men det er helt perfekt at du kom med svaret :)

Resultatet blevet som følger:

SELECT b.id AS basket_id, b.x AS basket_x, b.y AS basket_y, b.price AS basket_price, b.amount AS basket_amount, b.text AS basket_text, p.id AS product_id, p.price_number AS product_price_number, p.name AS product_name, p.description AS product_description, p.memo AS product_memo, p.picture AS product_picture, c.id AS category_id, c.name AS category_name, c.description AS category_description, ba.basket_addons_sum, bc.basket_colours_sum, bg.basket_glas_sum  FROM (
(
(
(
(
  basket_added b LEFT JOIN products p ON b.product = p.id)
  LEFT JOIN products_categories c ON p.category = c.id) LEFT JOIN (
  SELECT basket_id, SUM(price) AS basket_addons_sum
  FROM basket_addons
  GROUP BY basket_id) AS ba ON ba.basket_id = b.id) LEFT JOIN (
  SELECT basket_id, SUM(price) AS basket_colours_sum
  FROM basket_colours
  GROUP BY basket_id) AS bc ON bc.basket_id = b.id) LEFT JOIN (
  SELECT basket_id, SUM(price) AS basket_glas_sum
  FROM basket_glas
  GROUP BY basket_id) AS bg ON bg.basket_id = b.id) where b.session_id = 9612 GROUP BY b.id;

Men du må meget gerne lave et svar, så jeg kan acceptere det :)

Det er da noget kedeligt noget at du skulle ligge søvnløs over sådan en forespørgsel. Ærgerligt, at du ikke så mit svar før du gik i seng. :-)