Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    California
    Posts
    10

    Unanswered: MySQL 4.1.1 subqueries

    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

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: MySQL 4.1.1 subqueries

    Originally posted by Jen76
    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?

  3. #3
    Join Date
    Jan 2004
    Location
    California
    Posts
    10
    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;

  4. #4
    Join Date
    Jan 2004
    Posts
    24
    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
    http://www.phpscripts.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •