Hi,
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)
Does anybody know how I re-write the SQL to give me 306 items again.
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
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
Here are the two tables:
CREATE TABLE Category (CatParent Integer, Description varchar,
CategoryID integer NOT NULL DEFAULT '0', CategoryAbsolutePath
varchar, PRIMARY KEY(CategoryID))
CREATE TABLE Transn (GST double DEFAULT '0', AcctID integer, TransID
integer NOT NULL, CategoryID integer, Total double DEFAULT '0',
Description varchar, Date_Trans date, Notes varchar, NeedsAttention
boolean DEFAULT 'False', Reconciled boolean DEFAULT 'False',
GSTClaimed boolean DEFAULT 'False', HasSplitTrans boolean DEFAULT
'False', Currency varchar(10) DEFAULT 'AUD', CurrConverter float
DEFAULT '1.00', ForeignCurrencyAmount double DEFAULT '0', locked
boolean DEFAULT 'false', PRIMARY KEY(TransID))
--
Cheers,
Dr Gerard Hammond
We are on the cutting edge of catching up.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
