It's working now! Thank you Shawn and Alex. Have a Happy New Year! Pedro Almeida.
-----Mensagem original----- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviada: quarta-feira, 29 de Dezembro de 2004 21:23 Para: mail.pmpa Cc: mysql@lists.mysql.com Assunto: RE: SQL UNION ALL "mail.pmpa" <[EMAIL PROTECTED]> wrote on 12/29/2004 03:52:10 PM: > Thanks for replying. > > I tried that query but I get the following error: > > Query failed : You have an error in your SQL syntax... > ... right syntax to use near 'EXISTS ( SELECT * FROM new_products t03 WHERE > t03 > > I think I will have to forget the join, split and do a compare directly in > php. > > Also, I want to get n random unique results from a query, can it be done > using sql? > > Pedro Almeida. > > > -----Mensagem original----- > De: Alex S Moore [mailto:[EMAIL PROTECTED] > > On Wed, 29 Dec 2004 19:57:04 -0000 > "mail.pmpa" <[EMAIL PROTECTED]> wrote: > > > What I am trying to do is: > > Select products and join brand, but only products that are not new > products. > > What I have is: > > > > ( SELECT products.id, products.model > > FROM products, brands > > WHERE products.brand = brands.id ) > > UNION ALL > > ( SELECT products.id, products.model > > FROM products, new_products > > WHERE products.id != new_products.product_id ) > > SELECT t01.id, t01.model > FROM products t01 JOIN brands t02 > ON t01.brand = t02.id > WHERE NOT EXISTS ( > SELECT * FROM new_products t03 > WHERE t03.product_id = t01.id) > > Alex > This should work in v 3.23 and up and give you a random 10 products that are also brands but are not listed on new_products. If you are v3.22 or older, eliminate the "ORDER BY RAND()" and "LIMIT 10" clauses and pick randomly from the results using PHP. SELECT t01.id, t01.model FROM products t01 INNER JOIN brands t02 ON t01.brand = t02.id LEFT JOIN new_products t03 t03.product_id = t01.id WHERE t03.product_id is null ORDER BY RAND() LIMIT 10; Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
