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, 6307👍, 0💬
Popular Posts:
How do we create DCOM object in VB6? Using the CreateObject method you can create a DCOM object. You...
.NET INTERVIEW QUESTIONS - Is versioning applicable to private assemblies? Versioning concept is onl...
What Articles Have You Read about JUnit? There are a number of JUnit articles that you should read: ...
From performance point of view how do they rate ? Repeater is fastest followed by Datalist and final...
How Many Types of Tables Supported by Oracle? - Oracle DBA FAQ - Managing Oracle Database Tables Ora...