jQuery Grid Plugin - jqGridForum

Topic RSS

Related Topics sp_TopicIcon

SQL Command

Tags: sql

05/05/2010
11:19

aznan

Member

Members

Forum Posts: 32

Member Since:
14/01/2010

Offline

I hv 2 tables on purchase order. Main table: tbl_po which contain [int_poid, tbl_po.dt_appdate etc] and detail table: tbl_podetail which contain [int_podetail, int_po, int_qty, fl_unitpric] .

I try to put this inside my data url for my grid.. select option

SELECT tbl_po.int_poid, tbl_supplier.txt_supplier, SUM(tbl_podetail.int_qty*tbl_podetail.fl_unitprice) AS amount, tbl_po.dt_appdate FROM tbl_podetail INNER JOIN tbl_po ON tbl_podetail.int_poid = tbl_po.int_poid INNER JOIN tbl_supplier ON tbl_supplier.int_supplierid = tbl_po.int_supplierid INNER JOIN tbl_item ON tbl_item.int_itemid = tbl_podetail.int_itemid INNER JOIN tbl_metric ON tbl_metric.int_metricid = tbl_item.int_metricid GROUP BY tbl_po.int_poid, tbl_supplier.txt_supplier, tbl_podetail.fl_unitprice, tbl_metric.txt_metric, tbl_po.dt_appdate ORDER BY tbl_po.int_poid ASC

it runs OK if i omit some code and become like this

"SELECT tbl_po.int_poid, tbl_po.dt_appdate, tbl_supplier.txt_supplier FROM tbl_po, tbl_supplier WHERE tbl_po.int_supplierid = tbl_supplier.int_supplierid AND int_poid".$wh." ORDER BY ".$sidx." ". $sord." LIMIT ".$start." , ".$limit;

or is it not possible to run SQL command in jqgrid like SUM, MIN, AVG, INNER JOIN etc.. I run the same query inside phpmyadmin and it return some output. I look through some of the sample in demo, master detail, subgrid.. but I cant find the solution.. coz in the demo there is a field for amount in the master table.. in my case the amount is not exist in my tbl_po. I can create the (tbl_podetail.int_qty*tbl_podetail.fl_unitprice) as amount only on the "tbl_podetail" jqgrid like this

SELECT tbl_podetail.int_podetailid, tbl_podetail.int_poid, (tbl_podetail.fl_unitprice*tbl_podetail.int_qty) AS amount, tbl_podetail.dt_require FROM tbl_podetail WHERE int_poid=".$apid." ".$wh." ORDER BY ".$sidx." ". $sord." LIMIT ".$start." , ".$limit;

but this does not settle my problems.. i wish to create a grnd total to be passed to the amount.. just like the masted detail example…. coz i want to create some sort of summary..

hellpp pleaseee…

06/05/2010
11:20

aznan

Member

Members

Forum Posts: 32

Member Since:
14/01/2010

Offline

i manage to use SUM.. but still can't understand why INNER JOIN wont work... but my major problem is still givin me headache..!!!

HELLPPP!!

06/05/2010
11:35

aznan

Member

Members

Forum Posts: 32

Member Since:
14/01/2010

Offline

problem solve… my fault… everything is a.o.k.. I just rewrite or copy .. and voila it works GREAT!!! cant really explain .. Jqgrid ROCK!

hv I one last problem though, my search didnt work…. any idea where i should put the ".$wh." in my code???

$SQL = "SELECT tbl_po.int_poid, tbl_supplier.txt_supplier, CONCAT(tbl_item.txt_itemcode,':',tbl_item.txt_itemdescription) AS item, SUM(tbl_podetail.int_qty*tbl_podetail.fl_unitprice) AS amount, tbl_po.dt_appdate FROM tbl_podetail INNER JOIN tbl_po ON tbl_podetail.int_poid = tbl_po.int_poid INNER JOIN tbl_supplier ON tbl_supplier.int_supplierid = tbl_po.int_supplierid INNER JOIN tbl_item ON tbl_item.int_itemid = tbl_podetail.int_itemid INNER JOIN tbl_metric ON tbl_metric.int_metricid = tbl_item.int_metricid GROUP BY tbl_po.int_poid, tbl_supplier.txt_supplier, tbl_podetail.fl_unitprice, tbl_metric.txt_metric, tbl_po.dt_appdate ORDER BY ".$sidx." ". $sord." LIMIT ".$start." , ".$limit;

or do i need to do something else…

All RSS

Forum Timezone: Europe/Sofia

Most Users Ever Online: 715

Currently Online:
27 Guest(s)

Currently Browsing this Page:
1 Guest(s)