Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    24

    Question Unanswered: Union query doesn't return all results

    I have the following Union query however it doesn't appear to be working correctly.

    I have 4x Batch numbers to check, If I run the query separately (run it once per Batch number) - I get an accurate total of 256 records.

    However if I use the below query with all 4x Batch numbers together (as needed) it skips some results from the MYSTERY SHOP part of the query. The results also tend to vary depending on the order in which I key the Batch numbers?

    Sequence 1
    Batch1 - 42 ftp / 30 ms
    Batch2 - 30 ftp / 15 ms - it does not show these 15
    Batch3 - 63 ftp
    Batch4 - 76 ftp

    Sequence 2
    Batch2 - 30 ftp / 15ms
    Batch1 - 42ftp / 30ms - this time is doesn't show these
    Batch3 - 63ftp
    Batch4 - 76ftp

    So it looks like it's only attempting to search for the MS records for the first Batch number keyed. How can i fix this?

    Code:
    SELECT
    Advisor, 
    [Call Date], 
    [Monitor],
    [Batch Number],
    [Customer Account] AS [FTP Account Num], 
    NULL AS [MS Account Num],
    [Order Score], 
    [Add-On Score], 
    [Customer Experience Score], 
    Percentage, 
    Score AS [FTP Score], 
    NULL AS [MS Score],
    [Outsource Company]
    
    
    FROM [FTP Marking Sheet_Q]
    
    
    WHERE ((([FTP Marking Sheet_Q].[Batch Number])=[Enter batch number 1???]) Or  (([FTP Marking Sheet_Q].[Batch Number])=[Enter batch number 2???]) Or  (([FTP Marking Sheet_Q].[Batch Number])=[Enter batch number 3???]) Or  (([FTP Marking Sheet_Q].[Batch Number])=[Enter batch number 4???]) )
    
    
    UNION ALL SELECT 
    Advisor, 
    [Call Date], 
    [Monitor],
    [Batch Number],
    NULL AS [FTP Account Num],
    [Customer Account] AS [MS Account Num],
    [Order Score], 
    [Add-On Score], 
    [Customer Experience Score], 
    Percentage, 
    NULL AS [FTP Score],
    Score AS [MS Score], 
    [Outsource Company]
    
    
    FROM [MYSTERY SHOP Marking Sheet_Q]
    
    
    WHERE  ((([MYSTERY SHOP Marking Sheet_Q].[Batch Number])=[Enter batch number 1???]) Or  (([MYSTERY SHOP Marking Sheet_Q].[Batch Number])=[Enter batch number 2???]) And (([MYSTERY SHOP Marking Sheet_Q].[Batch Number])=[Enter batch number 3???]) And (([MYSTERY SHOP Marking Sheet_Q].[Batch Number])=[Enter batch number 4???]));

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use UNION ALL instead of UNION. See: SQL UNION Operator
    Have a nice day!

  3. #3
    Join Date
    May 2012
    Posts
    24
    I'm using UNION ALL on the line after the first WHERE declaration, am I using it correctly?

  4. #4
    Join Date
    May 2012
    Posts
    24
    I've solved it now, I've just realised that in my second WHERE clause I was using a 'And' instead of 'Or' - i've corrected this now and it's returning all results.

    Thanks anyway

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •