mySQL and Nested SQL Statements

Does anyone know if mySQL supports nested SQL statements? I seems that it does not, but then again, it's sometimes a little difficult to find specific answers on mysql.com.

For example, say you have a system were users are entering some bit of data to a table, called datatable. There is a seperate table, used for authentication called userdata, that has the user specific information in it (username, names, user id, etc).

However, say you want to be able to know which user added any given record. To ensure integrity, you add something unique to the user to each new record automatically based on their userid, or username... whatever (as opposed to them entering their name when they create the record). I assumed user id (the id field in the usertable).

But, now, on the same report as you are listing the records from datatable, you want to to display the username instead of id. You could do it the long way (as you loop through the rows returned from datatable, run as seperate query on the usertable...), but what I'd like to know is if mySQL supports nested queries, so that I don't have to actually do any PHP coding to get any data from the datatable, if I already know something from it, but nothing from the usertable:

For example... the following query would, in a single statement, answer the question "What is the username of the person who entered the record for data code 170420990000?" without first looking up the data code and finding the enteredby value, then looking up that value in the usertable and finding the username... see what I mean?



Oracle has pretty advanced support for this... I'm just wondering if mySQL does this, or something like it.

thanks
.c

Sorry, I can't give you an answer on nested SQL in MySQL.


As best I understand it: MySQL is an open source database that is good for a lot of applications, but doesn't have a lot of features offered by a database like Oracle. But, there is a licensed version of MySQL that you can buy that has a lot of features such as foreign key integrity which are unavailable in the free version: http://www.mysql.com . Do they offer nested SQL? I don't know.


I am wondering why you need nested SQL to accomplish what you want to do though. Couldn't you just do a join like this?

Yes, for the example I gave above, you could do that. I intentionally gave a simplistic example.

Here's a better example. Its a query that would find any asians in a table that are older than the current average age of all people in the the table, all in a single query



.c

Sorry I can't answer your question then but I did find this link that might help you out:

http://www.unixreview.com/documents/s=8989/ur0407e/


From that article, I guess the first question is what version of MySQL are you running? I've seen a few things that mentioned subqueries were not supported prior to version 4.1. But it looks like 4.1 is still in beta:

http://dev.mysql.com/downloads/

Cafu,

This answers my question:



Thanks for pointing out that article! (I past the excerpt above for the sake of search engines...)

.c