Home / Questions / 252627
Accepted
Antonio Ruiz
Asked: 2020-04-09 06:40:38 +0800 CST 2020-04-09 06:40:38 +0800 CST

How to get fields from 3 joined tables in MySQL?

  • 772

The accumulated VAT per line that each product (table_products) has in the table_order (add all 4%, all 10% and all 21% per line) will not be taken out.

  • Table_products

    id    | nombre    | iva | precio
1     | Producto1 | 4   | 20 
2     | Producto2 | 10  | 50

  • order_lines table

    id    | pedido  | id_producto | unidades  | total
1     | 100     | 1           | 1         | 20
2     | 100     | 2           | 1         | 50
3     | 101     | 1           | 1         | 20

I want to get the following result broken down by VAT type:

pedido  | iva4 | iva10 | iva21 | total
100     | 0.16 | 5     | 0     | 70
101     | 0.16 | 0     | 0     | 20

The total is the sum of the items in the order_table, this is if you save it. What is not taken is the accumulated VAT per line that each product in the order_table has (add all 4%, all 10% and all 21% per line).

mysql

2 Answers

  1. Best Answer

    You are looking for something like this:

    SELECT pedido,
  SUM(IF(iva=4,precio*unidades*iva/100,0)) iva4,
  SUM(IF(iva=10,precio*unidades*iva/100,0)) iva10,
  SUM(IF(iva=21,precio*unidades*iva/100,0)) iva21,
  SUM(precio*unidades) total
  FROM productos JOIN lineas_pedido ON id_producto=productos.id
  GROUP BY 1;

    In order for the site to work properly (and by the way, neither you nor me get a negative score), you must modify your question incorporating the structure of the tables in SQL with some registry for the tests (phpMyAdmin, Export) and thus we can mount a Minimal, complete and verifiable example .

    I am sending you the model that I had to generate to find the solution and you will see how to make it easier for us in the future to answer you:

    CREATE TABLE productos (
  id INT,
  nombre VARCHAR(31),
  iva INT,
  precio FLOAT
);

CREATE TABLE lineas_pedido (
  id INT,
  pedido INT,
  id_producto INT,
  unidades INT,
  total INT
);

INSERT INTO productos VALUES
(1, 'Producto1', 4, 20),
(2, 'Producto2', 10, 50);

INSERT INTO lineas_pedido VALUES
(1, 100, 1, 1, 20),
(2, 100, 2, 1, 50),
(3, 101, 1, 1, 20);

    By the way, for that data, the expected result would be:

    enter image description here

    If you also include the code of your attempt in your question, we can help you locate what has prevented you from solving the problem. SQL seems easy, but it has several elusive concepts, I encourage you to keep asking until you master it.

    I hope I have helped you with this specific question and ask questions on Stackoverflow. Cheer up with it.

    • 2

  2. I don't know how the query is but this could give you an idea

    selec i4.pedido as pedido, i4.iva as iva4, i10.iva as iva10, i21.iva as iva21 
from (` consulta para sacar pedido, sum(iva) as iva | iva=4`) as i4 
inner join (`consulta para sacar pedido, sum(iva) as iva | iva=10`) as i10 on i4.pedido=i10.pedido 
inner join (`consulta para sacar pedido, sum(iva) as iva | iva=21`) as i21 on i4.pedido=i21.pedido group by pedido;

    If you can already get the sum, you only need to join it

    • 0