RE: Union not handled properly

You're getting the expected result.  You'll need to specify "UNION ALL"
to get wha you want.

Check http://www.mysql.com/doc/en/UNION.html, especially:

"If you don't use the keyword ALL for the UNION, all returned rows will
be unique, as if you had done a DISTINCT for the total result set. If
you specify ALL, then you will get all matching rows from all the used
SELECT statements. "

This conforms to the standards, and the behavior of other DBMS's.

> > Hi Folks
> >
> > I have 3 tables, and 2 have the same number of records. So 
> if I issue
> > this command:
> >
> > select 'A', count(*)
> > from person
> > union
> > select 'B', count(*)
> > from role
> > union
> > select 'C', count(*)
> > from connexion
> >
> > I get:
> >
> > A 7213
> > B 7910
> > C 7910
> >
> > as expected. But if I drop the letters 'A' etc from the command:
> >
> > select count(*)
> > from person
> > union
> > select count(*)
> > from role
> > union
> > select count(*)
> > from connexion
> >
> > I only get:
> >
> > 7213
> > 7910
> >
> > I expected 3 lines of output in this case too.
> 
> 
> -- 
> Ron Savage, [email protected] on 9/10/2003. Room EF 312
> Deakin University, 221 Burwood Highway, Burwood, VIC 3125, Australia
> Phone: +61-3-9251 7067, Fax: +61-3-9251 7604 
> http://www.deakin.edu.au/~rons
> 
> 
> 
> -- 
> MySQL Bugs Mailing List
> 
> For list archives: http://lists.mysql.com/bugs
> To unsubscribe:    http://lists.mysql.com/[email protected]
> 
> 


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]