Ninja Cowgirl Ninja Cowgirl - 6 months ago 12
SQL Question

SQL query joining tables - subquery returned more than 1 value

I am trying to all the documents from alldocs (where siteid=something and checkout is not null). Alldocs has a fields call checkoutuserid and I need to get the name from the userinfo table. UserInfo may have same ID repeated and that's why I am adding site-id. I am getting an error on the following query. "Subquery returned more than 1 value)

SELECT AllDocs.LeafName
, AllDocs.CheckoutDate
, UserInfo.tp_Title AS [User Name]
, UserInfo.tp_Login AS [User ID]
, UserInfo.tp_Email
FROM AllDocs
INNER JOIN UserInfo ON AllDocs.CheckoutUserId = (
SELECT UserInfo.tp_ID
FROM UserInfo
WHERE UserInfo.tp_SiteID = 'E2FF98A7-B719-428D-8C30-856F08989691'
)
WHERE (AllDocs.SiteId = 'E2FF98A7-B719-428D-8C30-856F08989691')
AND (AllDocs.CheckoutUserId IS NOT NULL)
ORDER BY AllDocs.LeafName


I am adding following working code as I have added a 3rd table to the query. The following is working perfectly.

use WSS_Content_MMRHome1
SELECT distinct AllDocs.LeafName AS [File Name], AllDocs.dirName AS [Path], AllDocs.CheckoutDate,
UserInfo.tp_Title AS [User Name], UserInfo.tp_Login AS [User ID],
UserInfo.tp_Email AS [User Email], AllLists.tp_Title AS [List Name], ('http://inside.nv.com/'+AllDocs.DirName+'/'+AllDocs.LeafName) AS URL
FROM AllDocs
INNER JOIN UserInfo ON AllDocs.CheckoutUserId = UserInfo.tp_ID AND UserInfo.tp_SiteID = AllDocs.SiteId
INNER JOIN AllLists ON AllDocs.ListId = AllLists.tp_ID
WHERE AllDocs.SiteId = 'D36CCEA0-5351-4AEC-8B83-ACA2439CF38B'
AND AllDocs.CheckoutUserId IS NOT NULL
ORDER BY AllDocs.LeafName

Answer

No need for the subquery, just move the additional criteria to the join (or include in the where clause):

SELECT     distinct AllDocs.LeafName, AllDocs.CheckoutDate, 
           UserInfo.tp_Title AS [User Name], UserInfo.tp_Login AS [User ID], 
           UserInfo.tp_Email
FROM       AllDocs INNER JOIN
           UserInfo ON AllDocs.CheckoutUserId = UserInfo.tp_ID 
                AND UserInfo.tp_SiteID = 'E2FF98A7-B719-428D-8C30-856F08989691'
WHERE      AllDocs.SiteId = 'E2FF98A7-B719-428D-8C30-856F08989691'
                AND AllDocs.CheckoutUserId IS NOT NULL
ORDER BY AllDocs.LeafName

Actually looks like the same site id? If so, just join on that field as well:

...
FROM       AllDocs INNER JOIN
           UserInfo ON AllDocs.CheckoutUserId = UserInfo.tp_ID 
                AND UserInfo.tp_SiteID = AllDocs.SiteId
WHERE      AllDocs.SiteId = 'E2FF98A7-B719-428D-8C30-856F08989691'
                AND AllDocs.CheckoutUserId IS NOT NULL
ORDER BY AllDocs.LeafName
Comments