Steve, You nailed it dude! Thanks.
I'm pretty new and have been banging my head all morning. Whew! Won, Yeah... the table names were just examples (hopefully clear!) Once again, Thanks guys... really appreciate it! At 02:15 PM 11/9/01 -0500, you wrote: >That may work (you should put TR.1_id before "IN") but there is a more >straight forward way : > >SELECT TR.1_id >FROM Trick TR >WHERE NOT EXISTS ( > SELECT * > FROM Trick-Magician-Knows TMK > WHERE TMK.1_id = TR.1_id > AND TMK.2_id = ?) > >So it selects all tricks that do not have a corresponding entry in the >Trick-Magician-Knows table for a given magician. Switch the ? with 2_id >of the magician. > >You should probably come up with a better namign convention than 1_id and >2_id. It could get really confusing... > >On Fri, 9 Nov 2001, Won Lee wrote: > >> SELECT TR.1_id >> FROM TRICK TR >> WHERE NOT IN (SELECT 1_id FROM Trick TR2, Trick-Magician-Knows TMK WHERE >> TR2.1_id = TMK.1_id) >> >> Is you basic query. I don't know how and where you are getting the user >> inputed data to see which Magician you want to query, but you should get the >> basic idea. I also recommend you rethink those table names. >> >> -----Original Message----- >> From: Gonzo Rock [mailto:[EMAIL PROTECTED]] >> Sent: Friday, November 09, 2001 1:41 PM >> To: CF-Talk >> Subject: SQL Question >> >> >> A SQL Question... >> >> Bear with me... I'm kinda new here. >> >> I am trying to query table1, selecting records that do NOT have a relation >> in a subset from table3 that is related to table2. >> >> >> Example: >> >> table1(Trick table) >> 1_id,trickName,trickDescription >> >> table2(Magician table) >> 2_id,lastName,firstName >> >> table3(Trick-Magician-Knows table) >> 2_id,1_id >> >> if I choose some magician I want a list of tricks he/she DOES-NOT know. >> >> thanks for any insight ! >> Gonzo >> >> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.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
