How can I do this mysql difficult query-CodePudding

I want to try to do this query:

I have these tables:

Items Table

RUT          | Item_Id |  Period  |  Amount
15897554-4   |    8    | 2022-10  |  50000
18897554-9   |    3    | 2022-10  |  80000

Employees Table

RUT          |    Name
15897554-4   |    Luis    
18897554-9   |    Ramon

And I want to do this:

RUT          Name  Item_3_amount  Item_8_amount   Period
15897554-4   Luis      80000         50000       2022-10
18897554-9   Ramon       0              0        2022-10

I have tried this:

SELECT employees.rut, employees.name, () as Item_3_amount, () as Item_8_amount FROM employees

But I do not know how to take the amounts, can you help me? Thanks

CodePudding user response：

It appears you want totals on a per-employee basis for each particular item. In this case sample data, you are only showing a single row per person and a single item. What happens if you have multiple dates for a given person. Do you want the aggregated total for them? On a per-day basis? or showing a from/to time range period? Too many options, but I'll try to show what I THINK you are trying to go for.

Also, in the future, please format your SQL for readability vs one long single row. And dont put answers like a query in a comment. EDIT your original post and just add to the body of it.

Now, back to options. Get your joins first

select
      e.rut,
      e.name,
      case when i.item_id = 3 
           then amount else 0 end item_3_amount,
      case when i.item_id = 8
           then amount else 0 end item_8_amount,
      i.period
   from
      Employees e
         JOIN Items i
            on e.rut = i.rut
   order by
      e.rut

Now, if you wanted to do some aggregations, or filtering on specific periods, you could just adjust to use SUM() for the amounts and apply a WHERE clause for the periods such as

select
      e.rut,
      e.name,
      sum( case when i.item_id = 3 
           then amount else 0 end ) item_3_amount,
      sum( case when i.item_id = 8
           then amount else 0 end ) item_8_amount,
      i.period
   from
      Employees e
         JOIN Items i
            on e.rut = i.rut
           AND i.period = '2022-10'
   group by
      e.rut,
      e.name,
      e.period
   order by
      e.rut

CodePudding user response：

try this, you need to link the two tables.

SELECT employees.rut, employees.name, () as Item_3_amount, () as Item_8_amount, Items.period  FROM employees, Items where employees.rut = Items.rut

CodePudding user response：

Use a case statement to pivot the item amounts.

From your example it looks like you have the wrong end result, I'm assuming you meant to have the 80000 on the second record?

SELECT 
    employees.rut, 
    employees.name, 
    CASE WHEN items.Item_id = 3 THEN items.Amount ELSE 0 END as Item_3_amount,        
    CASE WHEN items.Item_id = 8 THEN items.Amount ELSE 0 END as Item_8_amount,
    items.Period 
FROM 
    employees
INNER JOIN
    items
    ON items.RUT = employees.RUT