Try this.
SELECT table1.1_id,
table3.1_id,
table3.2_id
FROM table1, table3
WHERE table3.2_id = #magicianid#
AND table1.1_id NOT IN table3.1_id
I didn't test it so not 100% that it works, but might get you started
Michael Corrigan
Programmer
Endora Digital Solutions
www.endoradigital.com
630/942-5211 x-134
----- Original Message -----
From: Gonzo Rock
To: CF-Talk
Sent: Friday, November 09, 2001 12:41 PM
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
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
