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, 7987👍, 0💬
Popular Posts:
What are different properties provided by Objectoriented systems ? Following are characteristic’s of...
How To Delete a User Account? - Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privilege...
Can you explain in brief how the ASP.NET authentication process works? ASP.NET does not run by itsel...
How To Change System Global Area (SGA)? - Oracle DBA FAQ - Introduction to Oracle Database 10g Expre...
Write down the equivalent pointer expression for referring the same element a[i][j][k][l]? a[i] == *...