c# - left join mysql database -

-

i have 3 tables( products,bill_details , bills) , want retrieve quantity of each product in bill_details table, below query give me products in bill_details table "just", , if bill_details empty, nothing retrieved !

enter image description here

and query:

select p.prod_id,p.prod_name, sum(b.de_quantity+b.de_bonus) - sum(bbb.de_quantity+bbb.de_bonus), p.prod_cost,p.prod_expdate,p.prod_bonusinfo,p.prod_note    (((products p left join bill_details b on p.prod_id=b.prod_id)  left join bill_details bbb on p.prod_id=bbb.prod_id ) left join bills on b.bill_id = a.bill_id)  left join bills aaa on bbb.bill_id = aaa.bill_id    a.cus_sup=1 , aaa.cus_sup=0 , a.archived=0 , aaa.archived=0  group p.prod_id,p.prod_name,p.prod_cost,p.prod_expdate, p.prod_bonusinfo,p.prod_note order p.prod_name asc;

your where clause checking against tables left joined. doing that, removing rows unmatched in left joins. not want.

instead, want move conditions in join clauses themselves, this:

select p.prod_id,p.prod_name, sum(b.de_quantity+b.de_bonus) - sum(bbb.de_quantity+bbb.de_bonus), p.prod_cost,p.prod_expdate,p.prod_bonusinfo,p.prod_note    (((products p left join bill_details b on p.prod_id=b.prod_id)  left join bill_details bbb on p.prod_id=bbb.prod_id ) left join bills on b.bill_id = a.bill_id , a.cus_sup=1 , a.archived=0)  left join bills aaa on bbb.bill_id = aaa.bill_id , aaa.cus_sup=0 , aaa.archived=0  group p.prod_id,p.prod_name,p.prod_cost,p.prod_expdate, p.prod_bonusinfo,p.prod_note order p.prod_name asc;

Comments

Post a Comment

Popular posts from this blog

Android : Making Listview full screen -

-
<linearlayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" tools:context=".mainactivity"> <edittext android:layout_margintop="5dp" android:layout_width="match_parent" android:layout_height="50dp" android:background="@drawable/location_edittext" android:id="@+id/locationeditext" android:textsize="15sp" android:textcolor="#999" android:paddingleft="15dp" /> <com.example.ravi_gupta.slider.viewpagercustomduration android:layout_width="wrap_content" android:layout_height="0dp" android:id="@+id/viewpager" android:scrollbarstyle="outsideoverlay" android:layout_weight="0.5...
Read more

javascript - Parse JSON from the body of the POST -

-
i'm using cameratag.com record video on website. service can send json response server can save data database. don't know how proceed here: the documentation states please note json repsonse passed in body of post , not form parameters. here example of how parse response in ruby: data = json.parse(request.raw_post) video_uuid = data["uuid"] but don't know how data on javascript. i'm not experienced in js please bare me :) post sample: { "uuid": "00012710-4b65-0131-ffe6-22000a499ea4", "camera_uuid": "98373a20-79ee-0130-3e42-1231390fcc11", "created_at": "2013-12-20t05:25:02.000z", "percent_complete": 100, "short_code": "kktb4q", "metadata": { }, "state": "published", "recorded_from": "http://singwho.com", "publish_type": null, "formats": [ { "name...
Read more

Revit Family Rename in a project -

-
is possible rename families in project through api. updating , standardizing our family naming convention. i'd build add-in rename families in existing project new standard names. haven't had success finding example of online. i’ve been able find family names, however, can’t rename family. i’m using c#. i tried direct way .name property , seems work. uiapplication uiapp = commanddata.application; uidocument uidoc = uiapp.activeuidocument; application app = uiapp.application; document doc = uidoc.document; foreach(elementid id in uidoc.selection.getelementids()) { familyinstance faminstance = doc.getelement(id) familyinstance; if (faminstance == null) continue; //skip element faminstance.symbol.name = faminstance.symbol.name + " - changed"; }
Read more