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

Gordon Linoff · Accepted Answer · 2014-12-03T12:38:59.023

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,

edited Dec 03 '14 at 12:38

answered Dec 02 '14 at 04:10

Gordon Linoff

1,242,037
58
646
786

Okay, what if i needed EmployeeName and StockID too? And what is the function of `xml path ('')), 1, 2, '') as RequestDetailIds,` the number 1, 2 – Athirah Hazira Dec 02 '14 at 04:13
1

@AthirahHazira . . . (1) You would join in the `user` table in the outer query; (2) This query has stock ids. – Gordon Linoff Dec 02 '14 at 04:13
Anyways, i've tried your query and there's an error that says `Conversion failed when converting the varchar value ', ' to data type int` – Athirah Hazira Dec 02 '14 at 04:19
Addition to my last comment, when i get rid of the comma, the query return the values like this `126127` I don't understand. I mean i've search everywhere and everyone uses comma or semicolon to seperate the values and it works fine. I've tried other samples too and the error is still on the string conversion – Athirah Hazira Dec 02 '14 at 08:28
1

@AthirahHazira . . . That would only occur if `StockId` or `RequestDetailsId` where numeric. But based on the same data in the question, they clearly are not. You can, of course, wrap them in `cast() as varchar(255))`. – Gordon Linoff Dec 02 '14 at 12:19
Thanks. It worked! :) I have one question tho. If i were to insert new lines instead commas `', '` , I use `CHAR(13)` but all it does is printing a string since the values were converted. Is there any way i can insert new lines? – Athirah Hazira Dec 03 '14 at 06:46
After your updates, the values were displayed into new lines but there's still a string in front of em. Even if i display the results to `text` – Athirah Hazira Dec 04 '14 at 02:08

Inner Join and Group Multiple Tables into one row

1 Answers1