D Patel
asked on
Complex SQL statement in VB.NET
Hello Experts,
I have loaded the basic MySQL data (i.e. data from the multiple tables using LEFT OUTER JOIN) into Tableview (VB.NET). Simultaneously, now I need to load the clubbed data (i.e. total weight)from the detail table (which has multiple lines similar to its parent table).
Below screenshots and existing code will help you to resolve my problem :
Above query works fine for me.
Now, I need to include another column in the tableview (i.e. NetWeight).
How do I write query so that it gives me the total weight and display in tableview (see below code).
The below separate query will work if run separately.
Please check the attachment for the sample output.
Waiting for your help.
Regards,
D Patel
Export_SQLQuery.xlsx
I have loaded the basic MySQL data (i.e. data from the multiple tables using LEFT OUTER JOIN) into Tableview (VB.NET). Simultaneously, now I need to load the clubbed data (i.e. total weight)from the detail table (which has multiple lines similar to its parent table).
Below screenshots and existing code will help you to resolve my problem :
Dim sql = "SELECT invo.InvoID, invo.dtInvo, invo.InvoNo" & _
" , ifnull(pack.PackNo,'Dos') as Pack,CONCAT(ifnull(cust.Custname,''),' - ', ifnull(cust.Ccode,'')) as Cust" & _
" , invo.Terms as Term, invo.InvoAmt, invo.DiscType" & _
" FROM(invo)" & _
" LEFT JOIN pack ON (invo.PackID = pack.PackID)" & _
" LEFT JOIN cust ON (pack.CustID = cust.CustID) where 1" & cond & _
" order by invo.InvoNo desc,invo.InvoID desc"
fill.fillDataTable(sql, Repeater1, conn)
conn.Close()
Above query works fine for me.
Now, I need to include another column in the tableview (i.e. NetWeight).
How do I write query so that it gives me the total weight and display in tableview (see below code).
The below separate query will work if run separately.
Dim sqlCal = "SELECT IFNULL(sum(packdet.Weight),0)" & _
" FROM(packdet)" & _
" where packdet.PackID=pack.PackID group by packdet.PackID"
Please check the attachment for the sample output.
Waiting for your help.
Regards,
D Patel
Export_SQLQuery.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Ryan Chong,
I also need "UnitName" field after net weight column.
How do I rewrite your query so that it also appear into the list?
the table relations are as below :
packdet.QtyUnitID = qtyunit.QtyUnitID
Please help...
Regards,
D Patel
I also need "UnitName" field after net weight column.
How do I rewrite your query so that it also appear into the list?
the table relations are as below :
packdet.QtyUnitID = qtyunit.QtyUnitID
Please help...
Regards,
D Patel
I also need "UnitName" field after net weight column.so i guess "UnitName" is from table: qtyunit?
then you may try:
SELECT invo.InvoID, invo.dtInvo, invo.InvoNo
, ifnull(pack.PackNo,'Dos') as Pack,CONCAT(ifnull(cust.Custname,''),' - ', ifnull(cust.Ccode,'')) as Cust
, invo.Terms as Term, invo.InvoAmt, invo.DiscType
, packdet.UnitName, packdet.nWeight
FROM(invo)
LEFT JOIN pack ON (invo.PackID = pack.PackID)
LEFT JOIN cust ON (pack.CustID = cust.CustID)
left join (
SELECT packdet.PackID, qtyunit.UnitName, IFNULL(sum(packdet.Weight),0) nWeight
FROM packdet inner join qtyunit
On packdet.QtyUnitID = qtyunit.QtyUnitID
group by packdet.PackID, qtyunit.UnitName
) packdet on pack.PackID = packdet.PackID
where 1 = 1
order by invo.InvoNo desc,invo.InvoID desc
ASKER
in table "packdet" the field "UnitName" does not exist.
it's from qtyunit table.
it's from qtyunit table.
in table "packdet" the field "UnitName" does not exist.
it's from qtyunit table.
check the subquery of "packdet":
left join (
SELECT packdet.PackID, qtyunit.UnitName, IFNULL(sum(packdet.Weight)
FROM packdet inner join qtyunit
On packdet.QtyUnitID = qtyunit.QtyUnitID
group by packdet.PackID, qtyunit.UnitName
) packdet on pack.PackID = packdet.PackID
ASKER
Thanks
ASKER
Thanks for your prompt help.
It has resolved my problem.
Again Thanks,
D Patel