Link to home
Start Free TrialLog in
Avatar of D Patel
D PatelFlag for India

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 :

User generated image
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()

Open in new window


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"

Open in new window


Please check the attachment for the sample output.

Waiting for your help.

Regards,
D Patel
Export_SQLQuery.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of D Patel

ASKER

Hi Ryan Chong,

Thanks for your prompt help.

It has resolved my problem.

Again Thanks,
D Patel
Avatar of D Patel

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.
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

Open in new window

Avatar of D Patel

ASKER

in table "packdet" the field "UnitName" does not exist.

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),0) nWeight
   FROM packdet inner join qtyunit
   On packdet.QtyUnitID = qtyunit.QtyUnitID
   group by packdet.PackID, qtyunit.UnitName
) packdet on pack.PackID = packdet.PackID
Avatar of D Patel

ASKER

Thanks