mysql - SQL Table Schema for Level -Sublevel Type Table -


i created 4 table gives hiearchical category list. categories table;

+----+--------------+---------------+----------------+ | id | categoryname | ref. category | else | +----+--------------+---------------+----------------+

ref. category ( link column 1)

as example;

+---+------------+---+------+ | 1 | elektronik | 0 | ...  | | 2 | drink      | 0 | ...  | | 3 | computer   | 1 | ...  | | 4 | mouse      | 3 | ...  | | 5 | keyboard   | 3 | ...  | | 6 | juice      | 2 | ...  | | 7 | mouse cable| 4 | ...  | +---+------------+---+------+

employees table:

+------------+------+---------+-----------+ | employeeid | name | surname | | +------------+------+---------+-----------+

orders table:

+---------+-------+------+-----------+ | orderid | empid | date | | +---------+-------+------+-----------+

orderdetails table:

+---------+-----------+----------+-------+------------+ | orderid | productid | quantity | price | categoryid | +---------+-----------+----------+-------+------------+

i want total prices of specific employees specific category , above levels (ref. categories 0 level) total prices employeeid 10

+-----------+------------+--------------+---------------+ | employeeid| categoryid | categoryname | total prices  | | 10        |    7       | mouse cable  | 3000          | | 10        |    4       | mouse        | 5000          | | 10        |    3       | computer     | 12000         | | 10        |    1       | elektronik   | 35000         | +-----------+------------+--------------+---------------+

i want create query starts specific refid , take categoryid, in next step, refid categoryid , continues refid 0

answer long below query works you

declare @rowresult table (     employeeid int,     categoryid int,     total int); declare @employeeid int; declare @categoryid int; declare @refid int;  set @employeeid = 100; set @categoryid = 7;  select @refid = refid categories categoryid = @categoryid;  while @refid > 0 begin      insert @rowresult     select e.employeeid, d.categoryid, sum(d.quantity * d.price) 'total'     employees e        inner join orders o on o.empid = e.employeeid       inner join orderdetails d on d.orderid = o.orderid     e.employeeid = @employeeid , d.categoryid = @categoryid     group e.employeeid, d.categoryid ;      set  @categoryid = @refid; select @refid = refid categories categoryid = @categoryid; end  select * @rowresult go

Comments

Post a Comment

Popular posts from this blog

What is happening when Matlab is starting a "parallel pool"? -

running parallel cpu processes in matlab starts command parpool() according documentation , function: [creates] special job on pool of workers, , [connects] matlab client parallel pool. this function takes bit of time execute, on order of 30 seconds. in other multi-cpu paradigms openmp, parallel execution seems totally transparent -- i've never noticed behavior analogous matlab (granted i'm not experienced parallel programming). so, happening between time parpool() called , when finishes executing? takes long? parallel computing toolbox enables run matlab code in parallel using several different paradigms (e.g. jobs , tasks, parfor , spmd , parfeval , batch processing), , run either locally (parallelised across cores in local machine) or remotely (parallelised across machines in cluster - either 1 own, or 1 in cloud). in of these cases, code run on matlab workers , copies of matlab without interactive desktop. if you're intending run on remo...
Read more

angular - DownloadURL return null in below code -

when change file updated in firebase storage when try downloadurl show null. filechange(event: any) { const imagefolder: string = this.employee.id; const filelist: filelist = event.target.files; const file: file = filelist[0]; const storageref = firebase.storage().ref().child(`${imagefolder}/profile.jpg`).put(file); console.log(storageref.snapshot.downloadurl); } the put() asynchronous. if want url after file uploaded have this: firebase.storage().ref().child(`${imagefolder}/profile.jpg`).put(file).then((snapshot) => { storageref = snapshot.downloadurl: console.log(snapshot.downloadurl); });
Read more

php - Cannot override Laravel Spark authentication with own implementation -

during login process of laravel spark need request token of remote api. api stores exact same users , passwords laravel spark application. therefore need username , non hashed password of user during authentication process. i thought overriding authenticated method solution problem. in routes/web.php override post /login endpoint , pointing endpoint own logincontroller: <?php namespace app\http\controllers\auth; use illuminate\support\facades\log; use illuminate\http\request; use laravel\spark\http\controllers\auth\logincontroller sparklogincontroller; class logincontroller extends sparklogincontroller { /** * create new login controller instance. * * @return void */ public function __construct() { parent::__construct(); } /** * handle successful authentication attempt. * * @param request $request * @param \illuminate\contracts\auth\authenticatable $user * @return response */ public fu...
Read more