Jason and all, Just checked, and confirmed that you can even do a self-join in M$ Access. There's even an Access help topic on self-joins. All ya gotta do is add a table to a query twice. Access appends _1 to the table name for the second copy. Then you can join the two tables and off you go. Ben -----Original Message----- From: Jason Lotz [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 25, 2001 11:00 AM To: CF-Talk Subject: Re: Query Join Brain Fart Ben, Thanks for the information. I never thought of doing it that way before. I has other applications in apps that use threads (i.e., forums.) I assume this will work in SQL Server as well? Jason ----- Original Message ----- From: "Braver, Ben" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, April 25, 2001 10:18 AM Subject: RE: Query Join Brain Fart > Hi Jason, > > You can do a "self join" to join a table to itself using an alias to > "pretend" there are two separate tables. This way you can join rows in a > table to rows in that same table. > > A classic example is an employee table with columns LastName, EmployeeID, > ManagerID and you want a list of people with their managers. Such as: use > the aliases "worker" and "manager" for the real "employee" table. (Oracle > syntax.) > > SELECT worker.lastname||' works for '||manager.lastname > FROM employee worker, employee manager > WHERE worker.ManagerID = manager.EmployeeID; > > Hope this applies to the question you were asking. > > Ben Braver > Information Technology > Ultramar Inc. > Golden Eagle Refinery > 150 Solano Way > Martinez, CA 94553-1487 > (925) 370-3673 voice > (925) 370-3393 fax > (510) 716-2557 pager > [EMAIL PROTECTED] > > > -----Original Message----- > From: Jason Lotz [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, April 25, 2001 9:49 AM > To: CF-Talk > Subject: Re: Query Join Brain Fart > > > How can you do a join on the same table? Both of these queries are drawn > from the CandidateSkills table? Am I misunderstanding your question? > > ----- Original Message ----- > From: "cassady " <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Wednesday, April 25, 2001 9:22 AM > Subject: Query Join Brain Fart > > > > Hey everyone - > > Arrgh. I haven't done any of these in a while, and I'm having a little bit > of a brain fart. Could somebody put these together in a join when the SQL > table returned has the following columns: > > > > CandidateID, Num_Skills, BM_score > > > > If I get the example then I think (hope? dream?) that my brain might start > functioning again like it should. :-) > > > > > > <!--- For Number of Skills ---> > > <cfquery name="q_BM_score" datasource="datasource"> > > Select CandidateID, Count(*) AS num_skills > > From CandidateSkills > > Where CandidateSkills.CandidateID IN (#ListCandidateID#) > > Group By CandidateID > > </cfquery> > > > > > > <!--- Query for skill points ---> > > <cfquery name="q_BM_score" datasource="datasource"> > > Select Sum(ExpYears) AS BM_score > > From CandidateSkills > > Where CandidateSkills.CandidateID = #Client_saSearch.CandidateID# > > AND ((CandidateSkills.SkillID = #Skills1#) > > OR (CandidateSkills.SkillID = #Skills2#) > > OR (CandidateSkills.SkillID = #Skills3#)) > > </cfquery> > > > > Thank you for your time in advance, > > Stephen R. Cassady > > [EMAIL PROTECTED] > > http://www.ububik.com > > http://www.tallylist.com <--- cause' you no you wannnnnnnna! > > > > Archives: http://www.mail-archive.com/[email protected]/ > > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
