UNION of result sets

• Stephen Knott

  SSC Eights!

  Points: 943

  More actions

  July 5, 2004 at 5:24 am

  #108626

  I am having some problems with UNION. From what I can understand, UNION can be used to concatenate one set of records with another.  I have a situation where I have 2 very similar tables, and am trying to create a view that shows certain fields (which are common to both tables), and shows all records from both tables.

  When I query my newly created view, it returns less records than even exist in the 1 table, let alone the UNION.  

  For example :

  SELECT COUNT(*) FROM [Job Journal Line] WHERE [Posting Date] >= '2004/06/30' AND [No_] = 'SRK'

  Returns 9 records.

  SELECT COUNT(*) FROM [Job Ledger Entry] WHERE [Posting Date] >= '2004/06/30' AND [No_] = 'SRK'

  Returns 0 records.

  I created the view as :

  CREATE   VIEW Timesheets AS

  SELECT [No_],[Posting Date],[Quantity],[Job No_], [Phase Code] FROM

  [Job Journal Line]

  UNION

  SELECT [No_],[Posting Date],[Quantity],[Job No_],[Phase Code] FROM

  [Job Ledger Entry]

  When I do the following :

  SELECT COUNT(*) FROM Timesheets WHERE [Posting Date] >= '2004/06/30' AND [No_] = 'SRK'

  I get 7 records, whereas 9 records exist for that criteria in [Job Journal Line]

  Something is not right. Any idea's on what I am doing wrong ?

   

   

   

   

   

   

   

• steven powell

  Mr or Mrs. 500

  Points: 557

  More actions

  July 5, 2004 at 5:35 am

  #513275

  try using "union all" instead of "union".  union will strip out any duplicates in the result set.

   

• Stephen Knott

  SSC Eights!

  Points: 943

  More actions

  July 5, 2004 at 5:59 am

  #513277

  Thanks. Worked perfectly.