Inner Join and Group Multiple Tables into one row

Question

I am using MSSQL Server and I would like to INNER JOIN and GROUP the data from multiple tables. But i got this error:

Column 'MyDB.dbo.USER.EmployeeID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Assume that i have tables as follow :

TABLE STOCK

    StockID        StockName
    ---------------------------
    1              StockOne
    2              StockTwo

TABLE USER

    EmployeeID    EmployeeName
    ---------------------------
    A001          ABC
    A002          ABCD

TABLE REQUISITION

    RequisitionID    RequestDetailsID    RequestNumber   EmployeeID
    ---------------------------------------------------------------
    1                1                   RN001           A001
    2                2                   RN001           A001
    3                3                   RN002           A002
    4                4                   RN003           A001

TABLE REQUISITION_DETAILS

    RequestDetailsID   EmployeeID    StockID
    ------------------------------------------
    1                 A001          1
    2                 A001          2
    3                 A002          1
    4                 A001          2

This is my SQL Syntax:

SELECT *

    FROM [MyDB].[dbo].[USER] A

    INNER JOIN
    [MyDB].[dbo].[REQUISITION] B
    ON
    B.EmployeeID = A.EmployeeID

    INNER JOIN  
    [MyDB].[dbo].[REQUISITION_DETAILS] C
    ON
    C.RequestDetailsID = B.RequestDetailsID

    INNER JOIN [MyDB].[dbo].[STOCK] D
    ON
    D.StockID = C.StockID

    WHERE EmployeeID = '$EpID'
    GROUP BY B.RequestNumber

EXPECTED OUTPUT

    RequestNumber   RequestDetailsID   EmployeeID    EmployeeName     StockID
    ------------------------------------------------------------------------------
    RN001           1, 2               A001          ABC              1, 2
    RN002           3                  A002          ABCD             1
    RN003           4                  A001          ABC              2

Answer 1

This is a tricky query for several reasons. First, SQL Server has a rather painful way of doing aggregate string concatenation. Then, you have the issue of connecting the request numbers and employees in the different tables.

From what I can tell, you only need two tables, requisition and requisition_details. The following query should be close to what you need:

select distinct r.RequestNumber, r.EmployeeId,
       stuff((select ', ' + cast(rd.RequestDetailsId as varchar(255))
              from requisition r2 join
                   requisition_details rd
                   on r2.RequestDetailsID = rd.RequestDetailsID
              where r2.RequestNumber = r.RequestNumber and r2.EmployeeId = r.EmployeeId
              for xml path ('')
             ), 1, 2, '') as RequestDetailIds,
       stuff((select ', ' + cast(rd.StockId as varchar(255))
              from requisition r2 join
                   requisition_details rd
                   on r2.RequestDetailsID = rd.RequestDetailsID
              where r2.RequestNumber = r.RequestNumber and r2.EmployeeId = r.EmployeeId
              for xml path ('')
             ), 1, 2, '') as StockIds
from requisition r

EDIT:

To insert newlines, I would just use:

       stuff((select '
' + cast(rd.RequestDetailsId as varchar(255))
              from requisition r2 join
                   requisition_details rd
                   on r2.RequestDetailsID = rd.RequestDetailsID
              where r2.RequestNumber = r.RequestNumber and r2.EmployeeId = r.EmployeeId
              for xml path ('')
             ), 1, 2, '') as RequestDetailIds,