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. ^^"
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.Obj
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.Obj
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)