Dr Gerard Hammond <g.hammond-WwWqvNR902a3Zbb/[EMAIL PROTECTED]>
wrote:
This SQL in a sqlite v2 database gives me 306 rows... This is what
I would expect.
SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total,
TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked FROM
Transn t left outer join Category c on c.CategoryID=t.categoryID
and AcctID in (3, 12, 11, 6, 28, 15) and t.categoryID is null ORDER
BY date_trans
I upgraded the db to a v3 db but I had some problems.
In SQL v3 the same SQL on the same upgraded database now gives me
8743 rows (ie every single row in the Transn table)
This is expected. If v2 behaved differently, there was a bug in it.
A left outer join produces every row from the left-hand-side table at
least once. That's the whole point of an outer join. If that's not what
you want, why do you use one?
Further, the query doesn't make any sense to me: when t.categoryID is
null, c.CategoryID=t.categoryID is always false (even null=null is
false), so you never actually retrieve any data from Category table and
could just as well not join with it at all.
Does anybody know how I re-write the SQL to give me 306 items again.
It would help if you described the structure of the two tables, and
explained what your query was supposed to achieve.
I thought this would have worked but it gave me zero rows. It appears
that you can't join a table if one value is a NULL.
SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total,
TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked FROM
Transn t, Category c where c.CategoryID=t.categoryID and AcctID
in (3, 12, 11, 6, 28, 15) and t.categoryID is null ORDER BY
date_trans
The expression (t.categoryID is null and c.CategoryID=t.categoryID) is
always false. If you mean to retrieve a cross-product of all records
where t.categoryID is null and c.CategoryID is null, say so (not that I
can imagine why anybody would want to do that).
Even this didn't work:
SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total,
TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked,
coalesce(t.categoryID, -1) as 't.categoryID' FROM Transn t,
Category c where c.CategoryID=t.categoryID and AcctID in (3, 12,
11, 6, 28, 15) and t.categoryID = -1 ORDER BY date_trans
Do you have any records in Category table where c.CategoryID = -1 ?
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
