-4

How to combine the two SQl Queries into one

In My case 3 Tables Name

Remain_cotton,add_cotton,Sell_Table

 conn.Execute _
    "INSERT INTO Remain_Cotton(No_Of_Bottle) " & _
    "SELECT sum(No_Of_Bottle)" & _
    "FROM add_cotton Where Cateogry='Large'"

   conn.Execute _
    "INSERT INTO Remain_Cotton(Quantity) " & _
    "SELECT sum(Quantity)" & _
    "FROM Sell_Detail Where Cateogry='Large'"

what I want

     conn.Execute _
    "INSERT INTO Remain_Cotton(No_Of_Bottle)(Quantity) " & _
    "SELECT sum(No_OF_Bottle),sum(Quantity)" & _
    "FROM add_cotton,Sell_Detail Where Cateogry='Large'"

I ask similar question Before but its time it is a SQL statement so Please Don't connect it with the previous one

Community
  • 1
  • 1
Sajjad
  • 11
  • 8
  • It would help if you post your actual code, relevant database schema, and the error messages you are seeing – Ian Kenney Feb 19 '14 at 08:44
  • **Dim large_tbl As String Dim sell_large As String **large_tbl = "SELECT Sum(No_Of_Bottle) FROM add_cotton where Cateogry='Large'" sell_large = "SELECT Sum(Quantity) FROM Sell_Detail where Cateogry='Large'" – Sajjad Feb 19 '14 at 08:47
  • better if you edit your question (did it for you this time) - you still don't show any code that runs the queries or anything that tries to handle the results, you don't explain what errors / results you are seeing – Ian Kenney Feb 19 '14 at 08:50
  • @Deanna Sorry??Will you please elaborate.... – Sajjad Feb 20 '14 at 13:20
  • This, [Store two Queries result in third variable](http://stackoverflow.com/q/21892124/588306), [Use SQL Statement in access 2007](http://stackoverflow.com/q/21892646/588306), and [Using Adodc control to subtract Queries Result](http://stackoverflow.com/q/21908078/588306) are all the same question. – Deanna Feb 20 '14 at 13:22

1 Answers1

1

If you're using SQL server you can use the EXCEPT 

Select Sum() From Table 1 Where Table=....** 
EXCEPT
Select Sum() From Table 2 Where Table 2=........**
owenrumney
  • 1,520
  • 3
  • 18
  • 37
  • Thanks but what about Visual basic is it possible to subtract two Query Result??? – Sajjad Feb 19 '14 at 08:44
  • Use LINQ EXCEPT so it would be something like `query3 = query1.Except(query2)` assuming whatever you're putting the data in in VB is IEnumerable. Can't you just get the correct dataset at the DB level? – owenrumney Feb 19 '14 at 08:47