Hi people!
------------------------------------------------------------------------
@Ryan,
> Fine, but do think of if you ever might want to use the DB via
> anything else, or a DB admin program... best is to ensure the DB
> schema itself knows the collation and case sensitivity requirements.
I have to take care of the case sensitivity through the interface of my
program, but I will also consider your comment.
> It is in fact hard to fathom a simple query where EXISTS is the only
> possible solution (i.e. the answer cannot be achieved with a simple
> JOIN or WHERE clause addition) which is why Keith said "your best
> bet is to..." and then demonstrated a way without using EXISTS.
in my present problem, I can use `unique`. I'm not at home know, but I
definitely have to play a little with your solutions.
------------------------------------------------------------------------
@Simon,
> you can submit this query and know that there is definitely an answer
> which is definitely a floating point value. And then in your
> programming language you can do your equivalent of
>
> IF (theanswer) > 0.0 THEN (doSomething) END
actualy I do that in C++,
if ( getTagCount( name ) == 0 ) // `0` means not present in the table
{
transaction.Add("INSERT INTO TAGS (NAME, COUNT) VALUES ('"+name+"',
0 );");
}
with `getTagCount()`, a function wich ask the db:
SELECT COUNT FROM TAG WHERE NAME = 'biology';
so if I got nothing from the query, `getTagCount()` returns 0 by itself
otherwise I convert the result given by the query to an integer.
but now, I try to merge all queries in one query since I heard about
`transactions`.
------------------------------------------------------------------------
@Igor,
> > sqlite> select count(a) from x;
>
> Better still:
>
> select exists (select 1 from x);
>
> The difference is that the version with count() always scans the
> whole table, while the latter stops at the first record - which is
> good enough if you only need to check for existence, and don't
> actually need the count.
that's a good stuff.
I will think a little, to store the result of some query like,
select exists (select 1 from Tags WHERE name='magnetohydrodynamics');
then using a case over that result...
CASE result WHEN 0 THEN addMe ELSE doNotAddMe END
I have to try, I never used case in Sqlite so far...
------------------------------------------------------------------------
thank you guys!
regards,
Nicolas
