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

sql merge two tables with different fields
sql merge two tables with same columns
sql merge two tables into one
how to combine two sql queries in one result
sql join
how to combine two select queries in sql with different columns
sql join multiple tables with conditions
how to retrieve data from two tables with one sql statement

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    

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.

Combining result tables from multiple SELECT statements, How do I combine two queries in SQL with different tables? Sometimes you ponder which SQL syntax to use to combine data that spans over multiple tables. JOIN is a syntax often used to combine and consolidate one or more tables. Tables are joined two at a time making a new table which contains all possible combinations of rows from the original two tables.

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

SQL: Combining the AND and OR Conditions, one table. How do you combine results from more than one SQL table? Fortunately there are three main ways you can combine data from multiple tables. In all cases, joins require two main ingredients: Two tables and a join condition​. There are some special conditions that must occur in order for a union to work​. In this step, you create the union query by copying and pasting the SQL statements. On the Create tab, in the Queries group, click Query Design. Close the Show Table dialog box. On the Design tab, in the Query group, click Union. Access hides the query design window, and shows the SQL view object tab.

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;

Combining and Negating Conditions with AND, OR, and NOT, This SQL tutorial explains how to use the AND condition and the OR condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, SQL Functions. AVG · COUNT · MAX · MIN · SUM. totn SQL In this example, we have a table called suppliers with the following data: Back to top. Using MAX with multiple tables. This will not work with a case statement because if two model numbers have the highest price, both need to display, and using a

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

How do I combine results from several SQL tables, Assuming pro1, criterium1 should be AA1, this produces the results you are looking for: SELECT pos.id, pro.id, pos.criterium1, pos.criterium2 , pro.criterium1,​  Second, the merge_condition determines how the rows from the source table are matched to the rows from the target table. It is similar to the join condition in the join clause. Typically, you use the key columns either primary key or unique key for matching. Third, the merge_condition results in three states: MATCHED, NOT MATCHED, and NOT MATCHED BY SOURCE. MATCHED: these are the rows that match the merge condition. In the diagram, they are shown as blue.

Joining two tables on multiple criteria, How to join two SQL tables and display the latest results. Recently a customer has The answer is to use JOIN and MAX statements in a query. Description. The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement.. When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition.

Using JOIN with MAX to write efficient queries, UNION allows you to stack one dataset on top of another dataset. SQL MIN/​MAX SQL Joins on Multiple Keys SQL joins allow you to combine two datasets side-by-side, but UNION allows you to statements, and to have the results of one statement display in the same table as the results from the other statement. Ways to join data from more than one table. When you want to see information from multiple tables, you can use a SELECT statement. SELECT statements can retrieve and join column values from two or more tables into a single row. The retrieval is based on a specified condition, typically of matching column values.

SQL UNION, In the previous tutorial, you learned how to query data from a single table using the SELECT To query data from multiple tables you use join statements. SQL  Select WeddingTable, TableSeat, TableSeatID, Name, Two.Meal FROM table1 as One inner join table2 as Two on One.WeddingTable = Two.WeddingTable and One.TableSeat = Two.TableSeat I only get one of the criteria 1/criteria 2 combinations even when I know for a fact that there are 3 or 4.

Comments
  • See meta.stackoverflow.com/questions/333952/…
  • thank you @Strawberry. ill make use of sqlfiddle next time
  • It's not too late to do it this time. In my experience, it immeasurably improves the quality of the answers.
  • The basic rule is, if a non-aggregated column is included in the SELECT then it must also appear in the GROUP BY
  • @Strawberry as per your comment I have re-worked the query.