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