Skip site navigation (1) Skip section navigation (2)

Re: SQL Question

From: Brian <Brrrian(at)Excite(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL Question
Date: 2001-11-12 18:54:22
Message-ID: 3.0.5.32.20011112105422.0094d7b0@pop.we.mediaone.net (view raw or flat)
Thread:
Lists: pgsql-novice
This was correctly answered on another list by Steve Miskovitz. 
I am posting it here for those of you who have been working this. There is more in depth info about the sql technique he uses in his solution at the SQL Tutor: 

http://w3.one.net/~jhoffman/sqltut.htm#More_Subqueries

It is essentially a NOT EXISTS in a (subquery)

Here is the answer which I copied and pasted making the appropriate table name and field changes into my real world application, (though magic could be cool.)
---------------------------
from: Steve Miskovitz <steve(at)collegepublisher(dot)com>
---------------------------
Subject: RE: SQL Question

here is a 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.



In response to

  • Re: at 2001-11-12 17:53:56 from John Burski

pgsql-novice by date

Next:From: amp Date: 2001-11-12 19:51:07
Subject: Re:
Previous:From: Joe MoracaDate: 2001-11-12 18:32:23
Subject: XML & Postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group