I have just upgraded from 4.0 to 4.1 specifically for the use of subqueries. But they do not seem to be working. I'm getting a MySQL Syntax Error. Help.....


Jen

Could you post the relevant SQL query and also relevant error?

I used an example subquery from the MySQL handbook...just trying to familiarize myself with them...here's the syntax:

SELECT DISTINCT Cust_ID FROM Cust_Billing_Info WHERE Cust_ID.Cust_Billing_Info IN ( SELECT Cust_ID FROM Customers);

Here's the error:

Failed to execute SQL : SQL SELECT DISTINCT Cust_ID FROM Cust_Billing_Info WHERE Cust_ID.Cust_Billing_Info IN ( SELECT Cust_ID FROM Customers); failed : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT Cust_ID FROM Customers)' at line 1

Now the alternate join syntax works:

SELECT DISTINCT Cust_Billing_Info.Cust_ID FROM Cust_Billing_Info, Customers WHERE Cust_Billing_Info.Cust_ID = Customers.Cust_ID;

From a dervived table standpoint this works for sure in 4.1.0 and 4.1.1.

SELECT * FROM Term, (SELECT CONCAT('%', subcategory, '%') as sub FROM SubCategory) subc WHERE term = sub
and Term.vol > 2500 and Term.price > 0.25
ORDER BY Term.price DESC

I would take the individual queries and run them by themselves and make sure they work fine. Actually after just looking at your query, I found the problem.

Your original query:
SELECT DISTINCT Cust_Billing_Info.Cust_ID FROM Cust_Billing_Info, Customers WHERE Cust_Billing_Info.Cust_ID = Customers.Cust_ID;

Your subquery:
SELECT DISTINCT Cust_ID FROM Cust_Billing_Info WHERE Cust_ID.Cust_Billing_Info IN ( SELECT Cust_ID FROM Customers);

The problem by look at it is that you have right after the where clause Cust_ID.Cust_Billing_Info, but based on the select Cust_ID is a field name and Cust_Billing_Info is a table name. So try the query like this.

SELECT DISTINCT Cust_ID FROM Cust_Billing_Info WHERE Cust_Billing_Info.Cust_ID IN ( SELECT Cust_ID FROM Customers);

You should be all setup now.

Donny