Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Subquaries in MySQL

Posted on 2003-03-15
Medium Priority
Last Modified: 2008-02-01
Hi everybody,

I have testes a subquary in my JSP application working with MySQL. But it seems that the subquary doesn't work and got an error message of syntax error.
The quary is as follow:

      String sql = "SELECT ID, Type, Name" +
        " FROM Objects" +
        " WHERE ParentID= " +
          " (SELECT ObjectID FROM Permissions" +
          " WHERE ObjectID=" + id +
          " AND UserID=" +
            " (SELECT ID FROM Users" +
            " WHERE UserName='" + userName + "'" +
            " AND Password='" + password + "'))" +
        " ORDER BY Type ASC";

Does anyone know how I can perform the same function without subquaries? Thank you for attention. ^^

Best Regards,

P.S. I can't switch to another database as it's required by my teacher. ^^"
Question by:vicshek
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 15

Accepted Solution

VGR earned 200 total points
ID: 8142478
"MySQL Server until version 4.0 only supports nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT .... "

in your case :
sql = "SELECT ID, Type, Name FROM Objects WHERE ParentID= (SELECT ObjectID FROM Permissions WHERE ObjectID=id AND UserID= (SELECT ID FROM Users WHERE UserName='userName' AND Password='password')) ORDER BY Type ASC";

I find this completely overkill and overcomplicated, at the limit of sophistication.

The first part could be written more simply as :

SELECT Objects.ID, Objects.Type, Objects.Name FROM Objects,Permissions WHERE Objects.ID=Permissions.ObjectID ORDER BY Objects.Type ASC;

think about what you try do get and incorporate your sub-sub-select ;-)

I guess something like this will do
SELECT Objects.ID, Objects.Type, Objects.Name FROM Objects,Permissions,Users WHERE Objects.ID=Permissions.ObjectID AND Permissions.UserID=Users.ID AND Users.userName='userName' AND Users.Password='password' ORDER BY Objects.Type ASC;

I recommend NOT trying to do this in a single query, but to make things clear and secure bu requesting the Users table first (identification), then only performing query (1)

Expert Comment

ID: 8145491
VGR ahd given you the right solution

Author Comment

ID: 8145615
Thx VGR. I think I will do your second suggestion..which is to use another function to select the UserID first...then pass it to the modified quary(1) provided by you.

Since there are quite a number of subquaires I have used...I need to change them all....really bad. ToT Hope MySQL will support the subquaries soon. Anyway, thx VGR for your help. ^^

Best Regards,
LVL 15

Expert Comment

ID: 8145761
not me.
it encourages people to be lazy writing overcomplicated queries while solution is simplier when you admit that SQL is a Query language, nor a (general-purpose) Programming language...
moreover, i hope people do understand that sub-sub-sub-sub-selects pose an efficiency problem and that performance will suffer. Relying of "optimizers" to do the job your brain should have done is always a bad idea imho.

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…
Suggested Courses

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question