MYSQL Multiple Group Concat -

-

hi have table called engineers , table called post_codes

when use following sql list of engineers , postcodes associated them using group concat statement cannot figure out how include in group concat (if indeed need one) list in field called secondary_post_codes_assigned post codes linked same engineer via secondary_engineer_id field. 

select      engineer.engineer,group_concat(post_code separator ', ') post_codes_assigned,     engineer.region,      engineer.active,      engineer.engineer_id engineer inner join post_code on engineer.engineer_id = post_code.engineer_id  group engineer_id

what need output similar this.

engineer_id | post_codes_assigned | secondary_post_codes_assigned   ---------- 1           | aw, aw3             | b12                          | 2           | b12                 | aw, cv12                     |

i hope clear pretty new mysql.

regards alan

you joining primary post codes , list them, same secondary ones.

select    e.engineer,   group_concat(distinct pc1.post_code) primary_post_codes_assigned,   group_concat(distinct pc2.post_code) secondary_post_codes_assigned,   e.region,    e.active,    e.engineer_id engineer e join post_code pc1 on e.engineer_id = pc1.engineer_id  join post_code pc2 on e.engineer_id = pc2.secondary_engineer_id group e.engineer_id;

as see, need distinct because when selecting primary , secondary postcodes, getting rows combinations of them in intermediate result. must rid of duplicates. reason ist better aggregate before joining. (which consider idea, may want make habit when working aggregates.)

select    e.engineer,   pc1.post_codes primary_post_codes_assigned,   pc2.post_codes secondary_post_codes_assigned,   e.region,    e.active,    e.engineer_id engineer e join  (   select engineer_id, group_concat(post_code) post_codes   post_code   group engineer_id ) pc1 on e.engineer_id = pc1.engineer_id  join  (   select secondary_engineer_id, group_concat(post_code) post_codes   post_code   group secondary_engineer_id ) pc2 on e.engineer_id = pc2.secondary_engineer_id;

a third option subqueries in select clause. prefer them in from clause shown, because easy add more columns subqueries, not possible in select clause.

select    e.engineer,   (     select group_concat(pc1.post_code)     post_code pc1     pc1.engineer_id = e.engineer_id   ) primary_post_codes_assigned,   (     select group_concat(pc2.post_code)     post_code pc2     pc2.secondary_engineer_id = e.engineer_id    ) secondary_post_codes_assigned,   e.region,    e.active,    e.engineer_id engineer e;

Comments

Post a Comment

Popular posts from this blog

php - How to display all orders for a single product showing the most recent first? Woocommerce -

-
i have password protected page on frontend. on page, want display full list of purchases associated single woocommerce product id. option 1: i've looked through woocommerce docs , , found: wc_cli_order , list_( $args, $assoc_args ) function lists orders. presumably, orders can filtered product id [--=] filter orders based on order property. line item fields (numeric array, started index zero): line_items.0.product_id option 2: i found article , based on customer id. i've modified code based on guesses meta_key , meta_value. $customer_orders = get_posts( array( 'numberposts' => -1, 'meta_key' => '_product', 'meta_value' => get_product_id(), 'post_type' => wc_get_order_types(), 'post_status' => array_keys( wc_get_order_statuses() ), ) ); how display orders single product showing recent order first? here solution put based on these articles: http://www.w...
Read more

asp.net - How to correctly use QUERY_STRING in ISAPI rewrite? -

-
i'm having difficult time , hope guys can help. using helicon isapi rewrite version 3.1.0.104 on our iis server. edit http.conf file , have tried , still fail. this trying do: redirect url: https://www.domain.com/switch-by-version?version=2.8.5.2594 to: https://test.domain.com/load/load.aspx?tver=2.8.5.2594 the version number @ end source url change, , need target url have same version number @ end of url example above shows. i tried following not work: rewritecond %{query_string} ^version=(\d\d?)\.(\d\d?)\.(\d\d?)\.(.*)$ [nc] rewriterule ^/switch-by-version(.*)$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l] any appreciated! thanks. if it's running in equivalent context of .htaccess , rule shouldn't start / . here's simpler version: rewritecond %{query_string} ^version=(\d+\.\d+\.\d+(?:\..+)?)$ [nc] rewriterule ^switch-by-version/?$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l]
Read more

angularjs - How restrict admin panel using in backend laravel and admin panel on angular? -

-
https://scotch.io/tutorials/token-based-authentication-for-angularjs-and-laravel-apps following tutorials, try write authentication restrict admin panel, didn't understood how can that. this admin panel, admin panel don't want show , request database unauthenticated users. my laravel-routes route::get('/admin',function(){ return view('index'); }); route::get('/api/v1/employees/{id?}', 'employees@index'); route::get('/api/v1/users/{id?}', 'employees@users'); route::post('/api/v1/employees', 'employees@store'); route::post('/api/v1/employees/{id}', 'employees@update'); route::delete('/api/v1/employees/{id}', 'employees@destroy'); route::group(['prefix' => 'api'], function() { route::resource('authenticate', 'authenticatecontroller', ['only' => ['index']]); route::post('authenticate', 'authentic...
Read more