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, 6832👍, 0💬
Popular Posts:
How To Run a JUnit Test Class? A JUnit test class usually contains a number of test methods. You can...
Can you have virtual functions in Java? Yes, all functions in Java are virtual by default. This is a...
What will be printed as the result of the operation below: #define swap(a,b) a=a+b;b=a-b;a=a-b; void...
interview.FYIcenter.com offers a collections of interview questions and answers for software and Web...
How To Set Up Breakpoints in Debug Mode? - Oracle DBA FAQ - Introduction to Oracle SQL Developer To ...