Help need to write a Query in VB - MsSql !

Hai ,

My database is Ms Sql and I am devolping in VB
The below is my query, it seems to be working but at the last there is some problem. If possible kindly correct the query. Actually the problem is the SalesIn Quantity value is not shown correctly, it seems to be working in SP which I created in Ms Sql, so I tried the same here, I hope with some modifications it could be correctly executed.

sql = "select Item as Itemid,Itemid as SoldItemId,Date as SoldDate,"
sql = sql & "(Qty * Unitcost) as Cost,Qty as SoldQty,ItemId as StockId,"
sql = sql & "Qty as StockQty,ItemId as SalesinId,Qty as SalesInQty from"
sql = sql & " Stock,Sales,StockDetail,SalesIn where Stock.Itemid *= Sales.Itemid AND "
sql = sql & "StockDetail.Itemid = Stock.Itemid AND SalesIn.ItemId = Sales.ItemId AND "
sql = sql & "StockDetail.WareHouse ='" & Text3.Text & "' AND SalesIn.Type ='" & Text4.Text & "' AND "
sql = sql & "Date Between '" & Text1.Text & "' AND '" & Text2.Text & "' Group By"
sql = sql & " Stock.ItemId,StockDetail.ItemId,Sales.ItemId,Sales In.ItemId,Sales.Date,Sales.Qty,Sales.UnitCost,Stoc kDetail.Qty,"
sql = sql & "SalesIn.Qty order by Stock.ItemId"
rst.Open sql, cnn, adOpenStatic, adLockReadOnly, adCmdText

Everything is coming correct except the SalesIn Quantity. If I remove and try means then the other things are showing correctly. I mean the ItemId and the Soldqty and the stockqty and everything is showing correctly here just for my reference I am showing all the ItemId.

Kindly view and reply me.

SalesTable, StockTable, StockDetail, SalesIn are the Four table I am taking here. All the four tables are Linked by the ItemId.

Thank you very much,
Chockyou need to qualify the columns in the SELECT the same way you have qualified them in the GROUP BY

suggestion: switch immediately to JOIN syntax rather than the "old style" joins using that darned asterisk beside the equal sign
select Stock.Item as Itemid
, StockDetail.Itemid as SoldItemId
, Sales.Date as SoldDate
, Sales.Qty * Sales.Unitcost as Cost
, Sales.Qty as SoldQty
, Stock.ItemId as StockId
, StockDetail.Qty as StockQty
, SalesIn.ItemId as SalesinId
, SalesIn.Qty as SalesInQty
from Stock
inner
join StockDetail
on Stock.Itemid = StockDetail.Itemid
left outer
join Sales
on Stock.Itemid = Sales.Itemid
left outer
join SalesIn
on Sales.ItemId = SalesIn.ItemId
where Sales.Date Between 'Text1.Text'
and 'Text2.Text'
and StockDetail.WareHouse = 'Text3.Text'
and SalesIn.Type ='Text4.Text'
group
by Stock.ItemId
, StockDetail.ItemId
, Sales.ItemId
, SalesIn.ItemId
, Sales.Date
, Sales.Qty
, Sales.UnitCost
, StockDetail.Qty
, SalesIn.Qty
order
by Stock.ItemIdfinal tip: never use a reserved word like Date as a column name

rudy
http://r937.com/|||Hi,

I modified the Left outer join as you said, the below is the query which i am currently using.

sql = "select Itemid as StockId,Itemid as StkDetailId,
ItemId as SoldItemId,ItemId as SalesinId,
SellingDate as SoldDate,"
sql = sql & "(SoldQty * UnitCost) as Cost,SoldQty as SoldQty,"
sql = sql & "StockQty as QtyInHnd,SaleinQty as SalesInQty from"
sql = sql & " Stock inner join StockDetail on
Stock.Itemid = StockDetail.Itemid"
sql = sql & " left outer join Sales on Stock.Itemid = Sales.Itemid"
sql = sql & " left outer join SalesIn on Sales.ItemId = SalesIn.Itemid"
sql = sql & " where Sales.Date Between '" & Text1.Text &
"' AND '" & Text2.Text & "' AND StockDetail.WareHouse ='" &
Text3.Text & "' AND "
sql = sql & "SalesIn.Type ='" & Text4.Text & "' Group By "
sql = sql & "Stock.Itemid,StockDetail.Itemid,Sales.Itemid,
SalesIn.Itemid,Sales.SellingDate,Sales.SoldQty,
Sales.UnitCost,StockDetail.QtyinHand,
SalesIn.SalesInQty order by Stock.ItemId

its executing, but I didn't get the output correctly, It didn't shows the record as per the Left Outer Join. Actually I need the output as below

StkId SoldCost QtyInHand SoldQty SaleInQty SDate SalesIn WareHouse
sl001 120 4 2 2 05/01/03 00 01
sl002 0 10 0 0 00 01
sl003 30 2 10 0 05/01/03 00 01
sl004 0 120 0 0 00 01

whethere the Item Sold or not all the Item Id should be listed with their details. The Stock and StockDetail is the Master for the ItemId, So i take that as Inner Join , now what I am getting the oupt put is

sl001 60 4 1 2 05/01/03 00 01
sl001 60 4 1 2 05/01/03 00 01
sl003 30 2 10 0 05/01/03 00 01

if above is not clear, I have attatched the Excel sheet.|||hai friend,

Now I am trying like this, will it work, is the way I am writing is correct or not. kindly let me know also now I will post this to UA,

here I am facing the error

Run-time error '-2147217900(80040e14)':
The Column Prefix 'subquery' doesnot match with a tablename or alias name used in the query.

sql1 = "select distinct ItemId,Description from Stock"
sql2 = "select Itemid,SoldQty,UnitCost,SoldDate from Sales where Sales.SoldDate Between ='" & Text1.Text & "' AND '" & Text2.Text & "'"

sql3 = "select distinct ItemId,WareHouse,StockQty from StockDetail where WareHouse ='" & Text3.Text & "'"

sql4 = "select distinct SalesType,ItemId,Date,ItemId,SalesInQty from Stock Left Join SalesIn on Stock.ItemId = SalesIn.ItemId where SalesInType ='" & Text4.Text & "' AND SalesIn.Date ='" & Text1.Text & "' AND '" & Text2.Text & "'

this is the Subquery

subquery = "select sql1.StockItemId,sql1.Description,sql2.SalesItemId ,"
subquery = subquery & "(sql2.SoldQty * sql2.Cost),sql2.SoldDate,sum(sql3.StockQty),"
subquery = subquery & "sum(sql4.SalesInQty) from "
subquery = subquery & "(((sql1 Left Join Sql2 on sql1.StockItemId=sql2.SaleItemId) Left Join sql3 on sql1.StockItemId = sql3.StockDetail.ItemId) Left Join sql4 on sql1.StockItemId = sql4.SalesInItemId) order by sql1.StockItemId"
this is the main query
mainquery = "select subquery.StockItemId,subquery.Decription,subquery. SoldItemId,"
mainquery = mainquery & "(subquery.SoldQty * subquery.Cost),subquery.SoldDate,subquery.StockQty ,"
mainquery = mainquery & "subquery.SalesInQty"

kindly when you have time view and reply me

Thankyou very much,
Chock.