CODEWITHSUNDEEP
mysqlsql
Irfan Harun
Irfan Harun

Reputation: 1059

best way to combine two sql tables with multiple conditions and one max condition

I've two tables : Materials and inventory.

I want to get the list of all the materials with their latest stock if available, otherwise just zero.

TABLE - MATERIALS 

id        | material    | 
==========================
1         |Lily - Blue    
2         |Lily - Orange    
3         |Lily - White
4         |Rose - Blue    
5         |Rose - Orange    
6         |Rose - White

TABLE - INVENTORY 

id        | date      | material   | final_stock | entry_time           | 
=========================================================================
1         |2018-05-18 |Lily - Blue |  30         |  2018-05-18 09:06:48 |        
2         |2018-05-18 |Lily - white|  10         |  2018-05-18 10:32:27 |        
3         |2018-05-18 |Lily - Blue |  90         |  2018-05-18 15:30:31 |

my query : 

select materials.material, max(inventory.final_stock) from materials left join 
inventory on materials.material = inventory.material

output

material    | max(inventory.final_stock)| 
========================================
Lily - Blue |90

output required >> 

date       | material     | max(inventory.final_stock)| 
======================================================
2018-05-18 | Lily - Blue  |90
2018-05-18 | Lily - Orange|0
2018-05-18 | Lily - white |10
2018-05-18 | Rose - Blue  |0    
2018-05-18 | Rose - Orange|0    
2018-05-18 | Rose - white |0

Upvotes: 1

Views: 75

Answers (4)

webmite
webmite

Reputation: 575

just add a sub-query to populate the missing elements, then use conditionals to fill in the Null fields. The below query matches the conditions asked for in your question.

Be aware in a 'live' system I would not just seek the max(final_stock) on any given date as the 'final_stock' item. If the 'entry_time' is supposed to the the most recent entry for a given stock item I would re-work this query to use that as the latest 'inventory entry' to be referenced. But to do so opens the issue of whether someone is allowed in your system to back-date inventory entries.

SELECT 
  ifnull(C.date,(SELECT max(date) FROM inventory)) AS date,
  C.material,
  ifnull(max(D.final_stock),0) AS final_stock 
FROM (
  SELECT
    max(B.date) AS date,
    A.material 
  FROM materials A LEFT JOIN inventory B ON A.material = B.material 
  GROUP BY A.material
  ) AS C LEFT JOIN inventory D ON C.material=D.material AND C.date = D.date 
  GROUP BY C.material,C.date
  ORDER BY C.material,C.date

results below

date        material        final_stock
2018-05-18  Lily - Blue     90
2018-05-18  Lily - Orange   0
2018-05-18  Lily - White    10
2018-05-18  Rose - Blue     0
2018-05-18  Rose - Orange   0
2018-05-18  Rose - White    0

If you are allowed to make use of the entry_time field then the query below is a better solution. This query also no longer has to use aggregation in the outer query. The result for your dataset is the same.

SELECT 
  ifnull(D.date,(SELECT max(date) FROM inventory)) AS date,
  C.material,
  ifnull(D.final_stock,0) AS final_stock 
FROM (
  SELECT
    max(B.entry_time) as entry_time,
    A.material 
  FROM materials A LEFT JOIN inventory B ON A.material = B.material 
  GROUP BY A.material
  ) AS C LEFT JOIN inventory D ON 
    C.material=D.material AND 
    C.entry_time = D.entry_time
    ORDER BY C.material

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can also do the JOIN with subquery :

select m.material, coalesce(i.final_stock, 0) as final_stock
from materials m 
left outer join (select material, max(final_stock) final_stock
                 from INVENTORY 
                 group by material  
                 ) i on i.material = m.material;

Upvotes: 0

Tolu
Tolu

Reputation: 175

Try this;

with inv as
(
select  m.material
       ,i.date
       ,i.final_stock
       ,Max(i.Entry_time) over (Partition By i.material) maxLog
from materials m
left join inventory i on m.material = i.material 
)
Select date, material, final_stock
From inv where entry_time = maxLog

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

One method is a correlated subquery:

select m.*,
       coalesce( (select i.final_stock
                  from inventory i
                  where i.material = m.material
                  order by i.entry_time desc
                  limit 1
                 ), 0) as final_stock
from materials m;

Note that one of the major reasons for having an id in a table is so it can be used for foreign key relationships. Hence, inventory should have a material_id column, not a material column.

Upvotes: 1

Related Questions