sql - Get multiple rows as comma separated string column AND map values to temp table from junction table -

-

i've seen several questions how pull multiple rows single comma-separated column t-sql. i'm trying map examples own case in need bring columns 2 tables referencing junction table. can't make work. here's query have:

with userscsv (useremails, siteid)  (select userssites.siteid, stuff(         (select ', ' + users.email          users          userssites.userid = users.id         xml path ('')         group userssites.userid     ), 1, 2, '')     userssites     group userssites.siteid ) select * userscsv

the hard stuff here based on this answer. i've added with which, understand it, creates sort of temporary table (i'm sure it's more complicated that, humor me.) hold values. obviously, don't need select values, i'm going joining table later. (the whole of need still more complicated i'm trying here.)

so, i'm creating temporary table named userscsv 2 columns i'm filling selecting siteid column userssites table (which junction table between users , sites) , selecting ', ' + users.email users table should give me email address preceded comma , space. then, chop first 2 characters off using stuff , group whole thing userssites.siteid.

this query gives me error identifying line 5 problem area:

column 'userssites.userid' invalid in select list because not contained in either aggregate function or group clause.

why should matter since column in question in where rather select stated in error? how can fix it? need users id matches id in junction table. i've got tons of users aren't mapped in table , have no need select them.

tl;dr- need temp table distinct sites in 1 column , comma-separated list of email addresses of related users in other. these 2 pieces of data come other tables , put using junction table on primary keys of 2 tables. hope makes sense.

select distinct us.siteid, stuff((select ', ' + u.email         users u          join userssites us2 on us2.userid = u.userid         us2.siteid = us.siteid             xml path('')), 1, 2, '') userssites

sql fiddle


Comments

Post a Comment

Popular posts from this blog

c++ - OpenCV Error: Assertion failed <scn == 3 ::scn == 4> in unknown function, -

-
i read alot other solution still confused should mine... #include <opencv2/objdetect/objdetect.hpp> #include <opencv2/highgui/highgui.hpp> #include <opencv2/imgproc/imgproc.hpp> #include <iostream> #include <stdio.h> using namespace std; using namespace cv; int main(int argc, const char** argv) { //create cascade classifier object used face detection cascadeclassifier face_cascade; //use haarcascade_frontalface_alt.xml library face_cascade.load("haarcascade_frontalface_alt.xml"); //setup video capture device , link first capture device videocapture capturedevice; capturedevice.open(0); //setup image files used in capture process mat captureframe; mat grayscaleframe; //create window present results namedwindow("outputcapture", 1); //create loop capture , find faces while (true) { //capture new image frame capturedevice >> captureframe; ...
Read more

php - render data via PDO::FETCH_FUNC vs loop -

-
i've been using 2 methods render data. the first one: function name($id,$name){ return '<div id="'.$id.'">'.$name.'</div>'; } echo implode($pdo->query("select id,name user")->fetchall(pdo::fetch_func,'name')); the second one: $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); foreach($users $user){ echo name($user->id,$user->name); } i don't understand how pdo::fetch_func works. tried figure out. however, not well-documented. could please explain fetch mode? , also, 1 performs better? thank you. both methods wrong , have learn how use templates , how separate business logic presentation logic. $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); tpl::assign('users', $users); is code business logic part. then in template <?php foreach $users $row): ?> <div id="<?=$...
Read more

The canvas has been tainted by cross-origin data in chrome only -

-
uncaught securityerror: failed execute 'getimagedata' on 'canvasrenderingcontext2d': canvas has been tainted cross-origin data i getting above error in chrome. code(following) works fine in mozilla. have not corss domain issue. why above error in chrome? var wheel_canvas, wheel_context, can_w, can_h, wheel_grd; var large_radius = 160; var small_radius = 120; var wheel_canvas = document.getelementbyid('wheel_canvas'); var wheel_context = wheel_canvas.getcontext('2d'); var can_w = $(wheel_canvas).width(); var can_h = $(wheel_canvas).height(); var centerx = can_w / 2, centery = can_h / 2; var wheel_grd = null; test('#ff0000'); function test(hex) { $('#clrpicker').css({'left': 210, 'top': 210 }); inverted = hexbw(hex); $('#color_val_show').val(hex); current_color_hex_val_selected = hex; $('#color_val_show').css({'color':inverted,'background':hex}); fillgradient...
Read more