Ninja Cowgirl Ninja Cowgirl - 6 months ago 19
SQL Question

Selecting Distinct row from from table

I have a main table (AllDocs) and I ran a query which gives me 145 records. And this is good. However, there are some fields (like listid and userid) so I decided to get the titles for listid and userid from 2 different table. Now, my result is 1870. I tried adding

distinct
to
select
and it reduced to 1530. The number of rows should be 145. What am I doing wrong?

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

Answer

Try using the group by statement. Additional rows are most likely coming back in the query.

SELECT AllDocs.SiteId, AllDocs.LeafName, AllDocs.ListId,
   AllDocs.CheckoutUserId, AllDocs.CheckoutDate,
   UserInfo.tp_Title AS [User Name], UserInfo.tp_Login AS [User ID], 
            UserInfo.tp_Email, AllLists.tp_Title AS [List Name]
FROM AllDocs
LEFT OUTER JOIN UserInfo ON AllDocs.CheckoutUserId = UserInfo.tp_ID
LEFT OUTER JOIN AllLists ON AllDocs.ListId = AllLists.tp_ID
WHERE (AllDocs.SiteId = 'E2FF98A7-B719-428D-8C30-856F08989691')
 AND (AllDocs.CheckoutUserId IS NOT NULL)
Group by AllDocs.SiteId, AllDocs.LeafName, AllDocs.ListId,
   AllDocs.CheckoutUserId, AllDocs.CheckoutDate, UserInfo.tp_Title UserInfo.tp_Login,  UserInfo.tp_Email, AllLists.tp_Title 
Comments