dev dev - 1 month ago 10
SQL Question

SQL - Using SELECT in the WHERE clause of another SELECT

i have next query

select No,
Description,
Item,
Date
from myTable1


and get

enter image description here

and just one select

select * from myTable2


and get

enter image description here

Now I need to select from MyTable1 all data and join with MyTable2

and get sum of quantity

group by NO and ITEM

where MyTable2.Date <= MyTable1.Date

Any idea?

I want to get this table

enter image description here

CSL CSL
Answer

Try this to get you started, without more info it is difficult to produce an accurate answer:

SELECT T1.No
     , T1.Item
     , SUM(T2.Quantity)
     , MIN(T1.Date) as Date FROM mytable1 T1
  INNER JOIN mytable2 T2 
       ON T1.No = T2.No AND T1.Item = T2.Item AND T2.Date <= T1.Date
  GROUP BY T1.No, T1.Item
Comments