CODEWITHSUNDEEP
sqlsql-server
c0micrage
c0micrage

Reputation: 1180

SQL get to select multiple names from select subquery

I am writing select statement in SQL Server. I have to add a select query.

select 
    acct.AccountID,
    acct.Username,
    acct.LastNm + ', ' + acct.FirstNm as Name ,
    acct.Lastlogin,
    acct.email as Email,
    (select acct2.FirstNm + ' ' + acct2.LastNm as Name from tblUserAccount acct2
              join tblReviewers ra2 on acct2.AccountID = ra2.ReviwerID) as Reviewers

from tblUserAccount acct

I need to get more names from a table called tblReviwers. So 1 user from the tblUserAccount table could be associated with multiple reviews.

The tblReviewers only has 3 column AnalystID, ReviewerID, and Date. When I select on the JOIN between TblUserAccount and TblReviewers on a test AccountID = AnalystID, I can get multiple ReviewerIDs, which their firstname and lastname are located in the tblUserAccount. This is the reason why I use a select subquery

When I run the query, I get the following error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I am trying to write a VIEW to get a data.

Any help is greatly appreciated.

Upvotes: 0

Views: 770

Answers (4)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112382

Subselects in select lists can only be used to return one value.

In your case, use joins

SELECT
    a.AccountID,
    a.Username,
    a.LastNm + ', ' + a.FirstNm as Name ,
    a.Lastlogin,
    a.email as Email,
    b.LastNm + ', ' + b.FirstNm as ReviewerName
FROM
    tblUserAccount a
    LEFT JOIN tblReviewers r ON a.AccountID = r.AnalystID
    INNER JOIN tblUserAccount b ON r.ReviewerID = b.AccountID

I also used a LEFT JOIN to the reviewers table, in case there is no reviewer defined for an analyst.

You will get several rows per analyst, if more than one reviewer is assigned to it. Usually I solve this problem in the front-end by making a report that puts the main data in a group header (the analyst) and the related date (the reviewers) in the detail section of the report.

Starting with SQL Server 2017, you can also use the STRING_AGG function to concatenate the values of string expressions.

SELECT
    a.AccountID,
    a.Username,
    a.LastNm + ', ' + a.FirstNm AS Name ,
    a.Lastlogin,
    a.email AS Email,
    STRING_AGG(
        ( SELECT b.LastNm + ' ' + b.FirstNm
          FROM
              tblReviewers
              INNER JOIN tblUserAccount b ON r.ReviewerID = b.AccountID
          WHERE
              r.AnalystID =  a.AccountID ),
        ', ') AS ReviewerName
FROM
    tblUserAccount a

This allows you to return more than one reviewer in one field.

Upvotes: 0

Elyor Murodov
Elyor Murodov

Reputation: 1028

Starting from Sql Server 2017, You can use something like STRING_AGG function, which combines values from multiple rows into a single string.

Then, your Reviewers column in your query might look like this:

(select STRING_AGG(Name, ',') from
    (select acct2.FirstNm + ' ' + acct2.LastNm as Name from tblUserAccount acct2
     join tblReviewers ra2 on acct2.AccountID = ra2.ReviwerID
     where ra2.AnalystID = acct.AccountID)
) as Reviewers

In this case, names(first name + last name) of the reviewers for the current user will be separated by commas.

Upvotes: 0

DRapp
DRapp

Reputation: 48139

A query for a column name can only contain a single record result set. If you have an entry with multiple results causes that error.

I think you are missing disclosure of a possible extra component needed in the reviewers table, the who entered it. This will result in a query similar to the following.

The aliases and relations will appear obvious, but need to be confirmed for your actual structure.

select 
      EnterBy.AccountID,
      EnterBy.Username,
      EnterBy.LastNm + ', ' + EnterBy.FirstNm as Name ,
      EnterBy.Lastlogin,
      EnterBy.email as Email,
      ReviewBy.FirstNm + ' ' + ReviewBy.LastNm as ReviewerName
   from 
      tblReviewers r
         tblUserAccount EnterBy
            on r.AccountID = EnterBy.AccountID
         tblUserAccount ReviewBy
            on r.ReviwerID = ReviewBy.AccountID

REVISION BASED ON ADDITIONAL DATA

Based on providing the analystID on who entered, you should be good with

select 
      EnterBy.AccountID,
      EnterBy.Username,
      EnterBy.LastNm + ', ' + EnterBy.FirstNm as Name ,
      EnterBy.Lastlogin,
      EnterBy.email as Email,
      STUFF(( Select 
                    ', [' + ReviewBy.LastNm + ', ' + ReviewBy.FirstNm + '] ' AS [text()]
                 From
                    tblReviewers r
                       JOIN tblUserAccount ReviewBy
                          on r.ReviwerID = ReviewBy.AccountID
                 where
                    r.AnaylstID  = EnterBy.AccountID
                 For XML PATH('')), 1, 2, '' ) as AllReviewers
   from 
      tblUserAccount EnterBy

Upvotes: 1

GMB
GMB

Reputation: 222482

You cannot have a column that contains multiple rows in a single result set, it just doesn't make sense from SQL perspective. Hence the error.

You should JOIN the tblReviewers table instead of subselecting it. That will yield all Reviewers. Then, JOIN again on the tblUserAccount table to get the name of the Reviewer, and you are all set.

SELECT 
    acct.AccountID,
    acct.Username,
    acct.LastNm + ', ' + acct.FirstNm as Name ,
    acct.Lastlogin,
    acct.email as Email,
    acct2.LastNm + ', ' + acct2.FirstNm as ReviewerName ,
FROM tblUserAccount acct
JOIN tblReviewers revi
    ON on acct.AccountID = revi.ReviewerID
JOIN tblUserAccount acct2
    ON on acct2.AccountID = revi.AnalystID

Upvotes: 0

Related Questions