Subquaries in MySQL

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,
Vic

P.S. I can't switch to another database as it's required by my teacher. ^^"
vicshekAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
VGRConnect With a Mentor Commented:
"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 :
(1)

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)
0
 
carchitectCommented:
VGR ahd given you the right solution
0
 
vicshekAuthor Commented:
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,
Vic
0
 
VGRCommented:
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.
regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.