>From http://dev.mysql.com/doc/mysql/en/union.html : "Before MySQL 4.1.1, a limitation of UNION is that only the values from the first SELECT are used to determine result column types and lengths. This could result in value truncation if, for example, the first SELECT retrieves shorter values than the second SELECT:"
You can either update to version 4.1.1 or later, when the problem was solved, or make sure that the first SELECT retrieves columns that are wide enough and character types. On 8/5/05, Kapoor, Nishikant <[EMAIL PROTECTED]> wrote: > I have a UNION whose statements when interchanged gives a different result. I > can understand the change in the order of the rows, but how is it that > 'picture' gets the correct value in (a) but not in (b)? Here are the two > queries and their results: > > (a) > (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE > te.person_id = u.uId AND te.tId IN (1) LIMIT 5) > UNION > (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE > te.person_id = a.person_id AND te.tId IN (1) LIMIT 5); > > last_name picture > Kapoor avataar02.png <from table u> > Manni 0 <from table u> > Office of Technology Assessment 0 <from table a> > Queue Readers 0 <from table a> > Milnes 0 <from table a> > > (b) > (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE > te.person_id = a.person_id AND te.tId IN (1) LIMIT 5) > UNION > (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE > te.person_id = u.uId AND te.tId IN (1) LIMIT 5); > > last_name picture > Office of Technology Assessment 0 <from table a> > Queue Readers 0 <from table a> > Milnes 0 <from table a> > Kapoor 0 <from table u> > Manni 0 <from table u> > > Thanks for your help, > Nishi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
