Make sure the subquery returns a scalar value. Either the roleDBNum column has to be unique or you need to apply an aggregate function to reduce to a single value. If the the subquery returns many values, use the IN operator instead of =.
Disclaimier: I'm still new to MySQL, but I'm assuming it works the same way as SQL Server :) --- Renee Henderson <[EMAIL PROTECTED]> wrote: > I have read through quite a bit of the MySQL manual > concerning subqueries and cannot > seem to get even the simplist of queries to work! > > I have a table called PersonRole which is the > many-to-many joint table between two > upper tables, Person and Role. I have tested the > two pieces of the query alone and > they work fine. (e.g. if I enter a "5" after the > equal sign, it works and if I just > type the subquery alone, it returns the single > number 5.) > > So, why does mysql give me a syntax error? > --------------------------------------------------------------- > SELECT personRoleDBNum FROM PersonRole > WHERE roleDBNum = (SELECT roleDBNum FROM Role WHERE > role = 'STAFF') > > ERROR 1064: 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 roleDBNum FROM > Role WHERE role = 'STAFF')' at line 2 > --------------------------------------------------------------- > > I must be dumb or just really really confused. Any > help would be greatly > appreciated. > > Renee > > > > > > ===== > ******************************************************** > Renee Henderson -- [EMAIL PROTECTED] (WA state) > Computer Programmer / Internet Specialist > > "When you were born, you cried and the world > rejoiced. Live your > life in such a way that when you die, the world > cries, and you > rejoice." > ******************************************************** > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __________________________________ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
