0

Need help for SQL SERVER Query

I have five tables, Roles, Requirements, Documents, RoleRequirments(. role has many requirements), RequirmentsDocuments (requirements has many documents).

And now I need Role document report there is not the direct connection between them but I am trying via requirement table.

I need all documents along with roles id. 

doc-id label roleids
 1      A       2,5,6
 2      B       2,1
 3      C       5 

SELECT d.title, d.id as doc_id, m.moduleId as mid , d.type,m.label, STUFF((SELECT ', ' + CAST(rc.roleid AS VARCHAR(10)) [text()] FROM edoc_link_role_req as rc WHERE rc.reqid= cd.reqid FOR XML PATH(''), TYPE) .value('.','NVARCHAR(MAX)'),1,2,' ') as roleid FROM edoc_link_role_req rc 
LEFT JOIN edoc_link_req_document cd ON cd.reqid = rc.reqid
LEFT JOIN edoc_document d ON cd.documentid = d.id 
WHERE d.installationid = 1
codeLover
  • 69
  • 7
  • I only count 4 tables? – Rich Benner Jun 22 '16 at 08:22
  • In query, i am using only three, – codeLover Jun 22 '16 at 08:23
  • You need `Roles LEFT JOIN RoleRequirments LEFT JOIN RequirmentsDocuments LEFT JOIN Documents` (pseudocode). Then you can reach the document via connected tables – Ruslan Bes Jun 22 '16 at 08:24
  • Then why mention that you have 5 tables and then state 4 if you're only using 3 in this query? – Rich Benner Jun 22 '16 at 08:24
  • I am able to reach documents but I need comma separated roles id for each document – codeLover Jun 22 '16 at 08:27
  • Do `LEFT JOIN edoc_document d ON cd.documentid = d.id AND d.installationid = 1` to get true left join behavior. (When in WHERE, you get regular inner join result...) – jarlh Jun 22 '16 at 08:30
  • @new_php_developer check out this question: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server Getting comma-separated ids isn't trivial in SQL server – Ruslan Bes Jun 22 '16 at 08:31

1 Answers1

0

Is it the equivalent of MySQL's GROUP_CONCAT you're after, i.e. join together roleids for each docid?

If so, see: Simulating group_concat MySQL function in Microsoft SQL Server 2005?

Community
  • 1
  • 1
Paul Crozer
  • 68
  • 1
  • 10