dev dev - 1 year ago 76
SQL Question

SQL - Using SELECT in the WHERE clause of another SELECT

i have next query

select No,
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

Answer Source

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

     , 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download