I have done a similar query for reporting purposes that took a very long time to run, but worked. Something like:
SELECT a.*, b.* FROM People a, Grades b WHERE a.PeopleID = b.PeopleID AND a.PeopleID IN (SELECT c.PeopleID FROM People c, Grades d WHERE c.PeopleID = d.PeopleID AND d.Grade = "A") >>> "Cantrell, Adam" <[EMAIL PROTECTED]> 03/28/02 11:31AM >>> Hypothetical SQL question: I have a table called PEOPLE and a table called GRADES. I'd like to return a list of people and all of their grades and then output the results in a cfoutput, grouping by peopleID. Ok, that's easy enough, but what if I only want to return people that have an A as one of their grades? What type of condition do I use to achieve this? Currently I'm able to pull the people that have an A, but it won't return the other grades, so it ends up with a bunch of people with just one or more A's - it won't show their B's, C's, and D's. This seems way to easy, but I'm struggling with it this morning for some reason, maybe someone can give me a little push. Adam. ______________________________________________________________________ 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
