Categories:
.NET (961)
C (387)
C++ (185)
CSS (84)
DBA (8)
General (31)
HTML (48)
Java (641)
JavaScript (220)
JSP (109)
JUnit (31)
MySQL (297)
Networking (10)
Oracle (562)
Perl (48)
Perl (9)
PHP (259)
PL/SQL (140)
RSS (51)
Software QA (28)
SQL Server (5)
Struts (20)
Unix (2)
Windows (3)
XHTML (199)
XML (59)
Other Resources:
How To Use Subqueries in the FROM clause
How To Use Subqueries in the FROM clause? - MySQL FAQs - SQL SELECT Statements with JOIN and Subqueries
✍: FYIcenter.com
If you have a query returning many rows of data, and you want to perform another query on those rows, you can put the first query as a subquery in the FROM clause of the second query. A subquery used in this way become a temporary table, and you must provide a table alias name for the subquery as in "SELECT ... FROM (SELECT ...) aliasName". The following statement shows you how to use a subquery as base table for the main query:
mysql> SELECT * FROM (SELECT l.id, l.url, r.comment FROM fyi_links l LEFT OUTER JOIN fyi_rates r ON l.id = r.id) WHERE url LIKE '%er%'; ERROR 1248 (42000): Every derived table must have its own alias mysql> SELECT * FROM (SELECT l.id, l.url, r.comment FROM fyi_links l LEFT OUTER JOIN fyi_rates r ON l.id = r.id) s WHERE s.url LIKE '%er%'; +-----+-------------------+-----------+ | id | url | comment | +-----+-------------------+-----------+ | 101 | dev.fyicenter.com | The best | | 102 | dba.fyicenter.com | Well done | | 103 | sqa.fyicenter.com | Thumbs up | | 107 | www.winrunner.com | NULL | +-----+-------------------+-----------+ 4 rows in set (0.06 sec)
2007-05-11, 6537👍, 0💬
Popular Posts:
How To Assign Query Results to Variables? - Oracle DBA FAQ - Working with Database Objects in PL/SQL...
What does a special set of tags <?= and ?> do in a PHP script page? <?= express...
What Is URI? URI (Uniform Resource Identifier) is a superset of URL. URI provides a simple and exten...
What is the difference between const char* p and char const* p? In const char* p, the character poin...
How To Give a User Read-Only Access to a Database? - MySQL FAQs - Managing User Accounts and Access ...