From: Nick Stone <nick@harelane.com>
To: pgsql-sql@postgresql.org
Subject: SQL Query question
Date: Thu, 30 Jun 2005 10:27:42 +0100
Message-ID: <20050630092944.E94022514BD@smtp.nildram.co.uk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nick@harelane.com
Subject: Re: SQL Query question
In-Reply-To: <20050630092944.E94022514BD@smtp.nildram.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox