mySQL and Nested SQL Statements

  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

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?

Code: [ Select ]
SELECT * from usertable WHERE id=(SELECT enteredby FROM datatable WHERE datacode=170420990000)


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

thanks
.c
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Cafu
  • Student
  • Student
  • Cafu
  • Posts: 97

Post 3+ Months Ago

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?


Code: [ Select ]
SELECT usertable.*
FROM usertable, datatable
WHERE usertable.id=datatable.enteredby
AND datatable.datacode=170420990000
  1. SELECT usertable.*
  2. FROM usertable, datatable
  3. WHERE usertable.id=datatable.enteredby
  4. AND datatable.datacode=170420990000
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

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

Code: [ Select ]
SELECT firstname,lastname,race
  FROM demographics
  WHERE age > (SELECT avg(salary) FROM demographics WHERE race = "asian")
  ORDER BY age ASC;
  1. SELECT firstname,lastname,race
  2.   FROM demographics
  3.   WHERE age > (SELECT avg(salary) FROM demographics WHERE race = "asian")
  4.   ORDER BY age ASC;


.c
  • Cafu
  • Student
  • Student
  • Cafu
  • Posts: 97

Post 3+ Months Ago

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/
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

Cafu,

This answers my question:

Quote:

One feature that MySQL has been lacking is the ability to do sub-queries. Sub-queries are queries that are run within other queries. They are executed before the primary or outer query and their results may be incorporated into the main query. MySQL's inability to perform sub-queries has long been a feature that has been used against it when compared to other SQL databases. As of version 4.1 of MySQL, however, sub-queries are now possible. This month, I will explore how to use sub-queries in MySQL.


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

.c

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: Bigwebmaster and 95 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.