why does my mysql does not accept subquery like this: SELECT LastName FROM customer WHERE CustId = (select CustId from orders);
mysql prompt this message: YOU HAVE AN ERROR IN YOUR SQL SYNTAX. CHECK THE MANUAL THAT CORRESPONDS TO YOUR MYSQL VERSION FOR THE RIGHT SYNTAX TO USE NEAR select CustId from orders) AT LINE 1
I am using version 4.0.16-nt
thanks for help.
I beleive that the MySQL team is working on support for Subqueries, (I think it’s available in 5.0 but it’s still in Alpha relase as of this writing)
Alternatively you can use joins. I prefer the method below.
SELECT a.LastName FROM customer a, orders b WHERE a.CustId = b.CustId Good luck
Sub query support is available in version 4.1.x of MySQL. This version is stable and doen’t have any major bugs in it, however its still considered beta, but mysql is known for having very stable/usable beta versions. I used Mysql 4.0.x when it was beta for a production server. Here is the portion of the mysql manual for subqueries http://dev.mysql.com/doc/mysql/en/Subqueries.html
Keith
You could also do the query as a JOIN to work with whichever version you use.
Something like:
SELECT customer.LastName from customer, orders WHERE orders.CustID = customer.custID;instead of
SELECT LastName FROM customer WHERE CustId = (select CustId from orders);
thanks but I have already try that join and it does not solve my problem.
I need to retrieve the names of customer who did not purchase a particular product. I could only retrieve the names of those who purchase.
please take a look at this query, I have use it but it gives me this message: Empty Set.
select c.Lastname,c.CustId,o.CustId,o.ProdId from customer as c,orders as o where o.CustId != c.CustId and o.ProdId != 00423
thanks for any help.
your looking for anyone who has not ordered Prod #00423. Looks good to me - have you tryed <> vice != and you currently have the product number as an int… should it be a string? parens maybe