X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3CBD252A37
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 30 Jun 2005 06:29:51 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 58785-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 30 Jun 2005 09:29:49 +0000 (GMT)
Received: from smtp.nildram.co.uk (smtp.nildram.co.uk [195.112.4.54])
	by svr1.postgresql.org (Postfix) with ESMTP id F348D52A34
	for <pgsql-sql@postgresql.org>; Thu, 30 Jun 2005 06:29:47 -0300 (ADT)
Received: from HYDROGEN (unknown [62.3.216.34])
	by smtp.nildram.co.uk (Postfix) with ESMTP id E94022514BD
	for <pgsql-sql@postgresql.org>; Thu, 30 Jun 2005 10:29:44 +0100 (BST)
From: "Nick Stone" <nick@harelane.com>
To: <pgsql-sql@postgresql.org>
Subject: SQL Query question
Date: Thu, 30 Jun 2005 10:27:42 +0100
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook, Build 11.0.6353
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Thread-Index: AcV9Vfcla/FOth4ySY+86tuPIAu4yg==
Message-Id: <20050630092944.E94022514BD@smtp.nildram.co.uk>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200506/370
X-Sequence-Number: 21971

Hi

Whilst I'm not new to SQL I am reasonably new to Postgres and as such I have
a question on the following query:

SELECT
    tbl1."TermTypeID",
    tbl1."ParentID",
    tbl1."KeywordID",
    tbl1."Term",
    tbl2."KeywordID"
FROM
    "Terms" As tbl1 LEFT JOIN
    "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" AND
tbl2."StockID" = 1
WHERE 
    (tbl1."TermTypeID" >= 200) AND
    (tbl1."TermTypeID" < 600) AND
    (tbl1."IsSynonym" = false) AND
    (tbl1."LanguageID" = 1)
ORDER BY
    tbl1."TermTypeID",
    tbl1."Term";

Why does the above query work fine and the folowing query not work? And as a
additional kind of spanner in the works I've tried the following on MS SQL
Server and Oracle both of which produce the correct results (i.e. the same
as the above query). NB: the Terms table always has data whereas the
SearchStore may or may not have any data.

SELECT
    tbl1."TermTypeID",
    tbl1."ParentID",
    tbl1."KeywordID",
    tbl1."Term",
    tbl2."KeywordID"
FROM
    "Terms" As tbl1 LEFT JOIN
    "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID"
WHERE 
    (tbl1."TermTypeID" >= 200) AND
    (tbl1."TermTypeID" < 600) AND
    (tbl1."IsSynonym" = false) AND
    (tbl1."LanguageID" = 1) AND
    (tbl2."StockID" = 1)
ORDER BY
    tbl1."TermTypeID",
    tbl1."Term";

Just to be obvious both queries as far as I can should return everything
from Terms and anything if it exists from SearchStore subject to the WHERE
clause parameters - obviously!

Many thanks in advance

Nick