UNION

• znkin

  Old Hand

  Points: 382

  More actions

  August 11, 2004 at 6:32 am

  #164583

  I have 2 sql queries joined with a UNION all

  select  count(*) from Table1

  UNION all

  select count(*) from Table2 where  Custid = 87312

  If I execute them separately

  The first query returns a number '120'

  and the second query returns '0' as there are no records for this condition

  Now when I use UNION all I get the resultset as

  120

  0

  Can I replace the second value that is 0 with ''(I mean nothing) which will give me

  120

  Thanks

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  August 11, 2004 at 7:13 am

  #518478

  In this limited scope I have FINALLY found a VALID use for CROSS JOIN

  USE pubs

  SELECT SUM(A1.RecCnt + A2.RecCnt)

    FROM (SELECT COUNT(*) RecCnt FROM dbo.authors) A1

      CROSS JOIN (SELECT COUNT(*) RecCnt FROM dbo.employee) A2

  SELECT COUNT(*) RecCnt FROM dbo.authors

  SELECT COUNT(*) RecCnt FROM dbo.employee

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• AKM

  SSCommitted

  Points: 1538

  More actions

  August 12, 2004 at 3:03 am

  #518616

  Are you saying that if there are no records meeting the Custid condition, then you want only 1 row in your resultset? If so, try this:

  select c from 
  ( select count(*) as c from table1 union all select count(*) as c from table2 where Custid = 87132 ) cs 
  where c > 0

  This might not work, I'm not at a SQL-connected machine at present

• Scalability Doug

  SSCertifiable

  Points: 5351

  More actions

  August 12, 2004 at 7:11 am

  #518652

  The following should work also:

  select sum(cnt) from

  ( select  count(*) cnt from Table1

  UNION all

  select count(*) from Table2 where  Custid = 87312

  ) a

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  August 12, 2004 at 6:34 pm

  #518791

  The reply from AKM is probably what you're looking for, unless you only wanted to suppress the second row.  If you always want the first row returned but the second row is optional you could use:

    select count(*) from Table1

    union all

    select count(*) from Table2

    where Custid = 87312

    group by CustID having count(*) > 0