> Is it possible to have a subquery from the same table to > output data based on a certain condition?
Yes. It's called a correlated subquery. It's described very well in the SQL Server Books Online: "Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book. USE pubs SELECT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_ID = authors.au_id) Here is the result set: au_lname au_fname ---------------------------------------- -------------------- White Johnson Green Marjorie Carson Cheryl Straight Dean Locksley Charlene Blotchet-Halls Reginald del Castillo Innes Panteley Sylvia Ringer Albert (9 row(s) affected) Unlike most of the subqueries shown earlier, the subquery in this statement cannot be resolved independently of the main query. It needs a value for authors.au_id, but this value is a variable. It changes as Microsoft� SQL Server(tm) examines different rows of the authors table." Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
