September 15, 2015 at 10:50 am
I am working with a query, having 2 subqueries.
The main query and the 2 subqueries each call the same table.
The desired output is a listing of all UserNames associated with tp_ListID='FB7', tp_DirName='Lists/Faculty', and having tp_IsCurrentVersion equal to either '0' or '1'.
The query results returns values other than those specified in the subqueries' WHERE clauses.
However, when I place the code below
(A.tp_ListId='FB7')
AND
(A.tp_DirName='Lists/Faculty')
AND
into the main query WHERE clause, the results are as they should be.
Here is the full query, below (I've commented out the code (from above) to mark where I've tested it.
Thanks for any help in this.
SELECT A.UserName, A.tp_ListID, A.tp_DirName, A.tp_IsCurrentVersion FROM AllUserData A
WHERE
--(A.tp_ListId='FB7')
--AND
--(A.tp_DirName='Lists/Faculty')
--AND
A.UserName IN
(
SELECT AUD.UserName
FROM AllUserData AUD
WHERE
(AUD.tp_IsCurrentVersion='0')
AND
(AUD.tp_ListId='FB7')
AND
(AUD.tp_DirName='Lists/Faculty')
)
)
and A.UserName IN
(
SELECT AUD2.UserName
FROM AllUserData AUD2
WHERE
(AUD2.tp_IsCurrentVersion='1')
AND
(AUD2.tp_ListId='FB7')
AND
(AUD2.tp_DirName='Lists/Faculty')
)
)
ORDER BY A.UserName
September 15, 2015 at 11:08 am
Here is an excellent place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply