Hi, I'm trying to do a simple UNION in MySQL (4.0.11a on Linux). I am trying the following ...
If I do SELECT THIS.PRODUCT_ID,"net.ajsoft.WebShop.Inventory.Product" AS JPOXMETADATA,THIS.ID FROM PRODUCT THIS LEFT OUTER JOIN BOOK SUBCLASS0 ON THIS.PRODUCT_ID = SUBCLASS0.BOOK_ID WHERE SUBCLASS0.BOOK_ID IS NULL AND THIS.PRICE < .15E3 i get +------------+--------------------------------------+-------+ | PRODUCT_ID | JPOXMETADATA | ID | +------------+--------------------------------------+-------+ | 3 | net.ajsoft.WebShop.Inventory.Product | P_005 | +------------+--------------------------------------+-------+ If I also do SELECT THIS.PRODUCT_ID,"net.ajsoft.WebShop.Inventory.Products.Book" AS JPOXMETADATA,THIS.ID FROM PRODUCT THIS INNER JOIN BOOK SUBCLASS ON THIS.PRODUCT_ID= SUBCLASS.BOOK_ID WHERE THIS.PRICE < .15E3; i get +------------+--------------------------------------------+-------+ | PRODUCT_ID | JPOXMETADATA | ID | +------------+--------------------------------------------+-------+ | 1 | net.ajsoft.WebShop.Inventory.Products.Book | P_003 | | 2 | net.ajsoft.WebShop.Inventory.Products.Book | P_004 | +------------+--------------------------------------------+-------+ Yet when I do SELECT THIS.PRODUCT_ID,"net.ajsoft.WebShop.Inventory.Product" AS JPOXMETADATA,THIS.ID FROM PRODUCT THIS LEFT OUTER JOIN BOOK SUBCLASS0 ON THIS.PRODUCT_ID = SUBCLASS0.BOOK_ID WHERE SUBCLASS0.BOOK_ID IS NULL AND THIS.PRICE < .15E3 UNION SELECT THIS.PRODUCT_ID,"net.ajsoft.WebShop.Inventory.Products.Book" AS JPOXMETADATA,THIS.ID FROM PRODUCT THIS INNER JOIN BOOK SUBCLASS ON THIS.PRODUCT_ID= SUBCLASS.BOOK_ID WHERE THIS.PRICE < .15E3; i get +------------+--------------------------------------+-------+ | PRODUCT_ID | JPOXMETADATA | ID | +------------+--------------------------------------+-------+ | 3 | net.ajsoft.WebShop.Inventory.Product | P_005 | | 1 | net.ajsoft.WebShop.Inventory.Product | P_003 | | 2 | net.ajsoft.WebShop.Inventory.Product | P_004 | +------------+--------------------------------------+-------+ Notice that the JPOXMETADATA column is incorrect for PRODUCT_ID=1 and 2. Any ideas anyone ? -- Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
