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
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
