From: | "Nick Stone" <nick(at)harelane(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | SQL Query question |
Date: | 2005-06-30 09:27:42 |
Message-ID: | 20050630092944.E94022514BD@smtp.nildram.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2005-06-30 10:39:25 | Re: ORDER records based on parameters in IN clause |
Previous Message | KÖPFERL Robert | 2005-06-30 08:01:49 | Re: ENUM like data type |