Post reply

UNION

  • 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

  • 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

  • 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

  • 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

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply