Subqueries in MySQL 4.1
I just discovered the potential of subqueries in MySQL. Although this possibility has been introduced a long time ago, but until now this was not interested ...
The reason was very simple - most of the servers on which I worked was to install older versions. Administrators are afraid to install new versions of databases for several reasons:
- uncertainty of action - MySQL is Open Source and is often modified. The number of trial versions, beta, development, and so prevented the implementation of any new products for commercial servers - would be far too frequent "crashes"
- problem with the transmission of data - the new engines can not interpret the database files from previous versions. The surest way to move is to export and import data, which in turn is very time-consuming
- some companies adhere to the policy - why modify something that works well? Indeed, the lack of subqueries in MySQL you could "get around" doing a loop in PHP.
How to use subqueries? For example I developed my problem:
We are looking for complete data (title, content, etc.). Recent article in each section.
Using a version older than 4.1 problem could be solved in two ways:
- Make a query for searching all the articles and groups them wegług department. Then we loop PHP and for each heading we find a (recent) article.
- We do ask that look after the emergence of the latest articles in the departments - SELECT `department`, MAX (`date`) GROUP BY `id_dzial`. Then look for these articles. After proper dissected the results of previous inquiries, searching for details of these articles can obsłóżyć "single select" (*)
It is this second method used to create a single query with subqueries. It will look like this:
SELECT * FROM `articles` WHERE (`department`, `data`)
IN
(
SELECT `department`, MAX (`date`) FROM `articles` GROUP BY `department`
)
ORDER BY `date` DESC
Part of this is highlighted by the subquery. I hope that the code is understandable. It works exactly the same as the solution to (*), except that all data processing is done on the MySQL - PHP get a concrete answer to put the problem. This simplifies a very complicated and readability of application code.
Unfortunately, some hosting companies are still not on their servers MySQL'aw version 4.1 or later. I would recommend this company - their hosting certainly supports MySQL 4.1. I know because I am using 
or recommend other social networking sites:
Luke Jarzembowski, author of the blog - since 2006 under the illusion that it will be rich ... ;-) But still working hard on this. Programs , blogging , and positions , creating and promoting their e-businesses . 
Entries similar to "Subqueries in MySQL 4.1"
- No related posts
