Categories:
.NET (357)
C (330)
C++ (183)
CSS (84)
DBA (2)
General (7)
HTML (4)
Java (574)
JavaScript (106)
JSP (66)
Oracle (114)
Perl (46)
Perl (1)
PHP (1)
PL/SQL (1)
RSS (51)
Software QA (13)
SQL Server (1)
Windows (1)
XHTML (173)
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, 6911👍, 0💬
Popular Posts:
What is the quickest sorting method to use? The answer depends on what you mean by quickest. For mos...
What is PMP(project management plan)? The project management plan is a document that describes the p...
Can you explain in GSC and VAF in function points? In GSC (General System Characteristic) there are ...
What is synchronization and why is it important? With respect to multithreading, synchronization is ...
How To Check the Oracle TNS Settings? - Oracle DBA FAQ - ODBC Drivers, DSN Configuration and ASP Con...