subquery with unexpected result

• cajun_sql

  SSCommitted

  Points: 1842

  More actions

  September 15, 2015 at 10:50 am

  #320540

  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

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  September 15, 2015 at 11:08 am

  #1826833

  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/