title: SQL query joining tables - subquery returned more than 1 value
body: <p>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)</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I am adding following working code as I have added a 3rd table to the query. The following is working perfectly. </p>
  
  <pre><code>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
  </code></pre>