From rlpowell@digitalkingdom.org Thu Jun 03 19:15:32 2004
Received: with ECARTIS (v1.0.0; list lojban-list); Thu, 03 Jun 2004 19:15:32 -0700 (PDT)
Received: from rlpowell by chain.digitalkingdom.org with local (Exim 4.32)
	id 1BW4Em-00032e-Bb
	for lojban-list@lojban.org; Thu, 03 Jun 2004 19:15:16 -0700
Date: Thu, 3 Jun 2004 19:15:16 -0700
To: lojban-list@lojban.org
Subject: [lojban] Re: Urgent: SQL help for BPFK.
Message-ID: <20040604021516.GF26987@chain.digitalkingdom.org>
Mail-Followup-To: lojban-list@lojban.org
References: <20040604012056.GE26987@chain.digitalkingdom.org> <200406032203.04635.phma@phma.hn.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200406032203.04635.phma@phma.hn.org>
User-Agent: Mutt/1.5.5.1+cvs20040105i
From: Robin Lee Powell <rlpowell@digitalkingdom.org>
X-archive-position: 8044
X-ecartis-version: Ecartis v1.0.0
Sender: lojban-list-bounce@lojban.org
Errors-to: lojban-list-bounce@lojban.org
X-original-sender: rlpowell@digitalkingdom.org
Precedence: bulk
Reply-to: lojban-list@lojban.org
X-list: lojban-list

On Thu, Jun 03, 2004 at 10:03:04PM -0400, Pierre Abbat wrote:
> On Thursday 03 June 2004 21:20, Robin Lee Powell wrote:
> > I have a table with message_id and in_reply_to.
> >
> > I need to select all rows that have an in_reply_to that is not equal
> > to *any* message_id anywhere in the same table.
> >
> > This is MySQL, so I don't *think* I can use sub-selects.
> >
> > This is rather urgent, as it's breaking the BPFK boards.
> 
> Never done this in one table, and most of my SQL experience is in
> Postgres, but it should be something like this:
>
> select * from cartu as zunle left outer join cartu as pritu on
> zunle.in_reply_to=pritu.message_id where pritu.in_reply_to is null;

Yep.  Thanks.

select distinct tk1.in_reply_to from tiki_comments tk1 left outer join
tiki_comments tk2 on tk1.in_reply_to = tk2.message_id where
tk2.message_id is null;

This only partly solves the problem, unfortunately, because I need to
wedge it into a larger expression, but I think I can work it out.

BTW, once the boards are working again (which should be momentarily),
you owe the group responses.

-Robin

-- 
http://www.digitalkingdom.org/~rlpowell/  ***  I'm a *male* Robin.
"Many philosophical problems are caused by such things as the simple
inability to shut up." -- David Stove, liberally paraphrased.
http://www.lojban.org/  ***  loi pimlu na srana .i ti rokci morsi