sql - How can I restrict a query that uses STUFF to only return one record for each group of values stuffed rather than one for each stuffed item? -


with query:

select s.unit, lu.reportname, s.nextexecution,         stuff((select ','+emailaddr reportsunitemails e s.unit = e.unit , s.reportid = e.reportid xml path('')),1,1,'') allemailaddresses,         s.nextexecutionsbegindatearg, s.nextexecutionsenddatearg  reportsscheduler s  full join reportslu lu on s.reportid = lu.reportid  full join reportsunitemails e on s.unit = e.unit , s.reportid = e.reportid  order s.unit, s.reportid 

...i want same count of records exists in rreportsscheduler table, in fact getting 1 record each related email address (emailaddr reportsunitemails).

where there 1 email address, 1 record returned; there 4 email addresses, 4 records returned; etc.

so question is: can "distinctify" complex query return 1 record each email address (while still stuffing them "allemailaddresses").

i tried this:

select s.unit, lu.reportname, s.nextexecution,         distinct(stuff((select ','+emailaddr reportsunitemails e s.unit = e.unit , s.reportid = e.reportid xml path('')),1,1,'') allemailaddresses),         s.nextexecutionsbegindatearg, s.nextexecutionsenddatearg  reportsscheduler s  full join reportslu lu on s.reportid = lu.reportid  full join reportsunitemails e on s.unit = e.unit , s.reportid = e.reportid  order s.unit, s.reportid 

...but refuses comp[ile,ly], saying, "error 156: incorrect syntax near keyword 'distinct'. incorrect syntax near keyword 'for'."

not being sqlhead, shot in dark, anyway.

how can have stuff, fluffier, not stuffier? i'm wondering if self join might in future, don't know if that's so, , if so, how implement it.

update

both accepted answer , this:

select distinct s.unit, lu.reportname, s.nextexecution, s.reportid,        stuff((select ','+emailaddr reportsunitemails e s.unit = e.unit , s.reportid = e.reportid xml path('')),1,1,'') allemailaddresses,         s.nextexecutionsbegindatearg, s.nextexecutionsenddatearg reportsscheduler s  full join reportslu lu on s.reportid = lu.reportid  full join reportsunitemails e on s.unit = e.unit , s.reportid = e.reportid  order s.unit, s.reportid 

...work, i'm going accepted answer, seems "more better."

you need reportsunitemails table inside subquery stuff. commented out join reportsunitemails in outer query. should work.

select s.unit, lu.reportname, s.nextexecution,         stuff((select ','+emailaddr reportsunitemails e s.unit = e.unit , s.reportid = e.reportid xml path('')),1,1,'') allemailaddresses,         s.nextexecutionsbegindatearg, s.nextexecutionsenddatearg  reportsscheduler s  full join reportslu lu on s.reportid = lu.reportid  --full join reportsunitemails e on s.unit = e.unit , s.reportid = e.reportid  order s.unit, s.reportid 

Comments

Popular posts from this blog

java - Suppress Jboss version details from HTTP error response -

gridview - Yii2 DataPorivider $totalSum for a column -

Sass watch command compiles .scss files before full sftp upload -