[sqlite] help with query

[e-mail mgbg25171]

Simon...

Yes I forgot the "where sn.nm='std1';" restriction and...

also see you've used 2 inner joins.

Thank you very much for your thoroughness.

It's very much appreciated.

> 
> On 06 April 2016 at 12:41 Simon Davies 
> wrote:
> 
> 
> On 6 April 2016 at 12:22, e-mail mgbg25171 
> wrote:
> > Here are my tables specified as... tbl_nm | col1, col2...
> > std_nms | id, nm
> > raw_nms | id, nm
> > nm_pairs | id, std_nms_id, raw_nms_id
> >
> > I'm wondering how to supply a single std_nms.nm and get back a list of
> > pairs
> > i.e. std_nm.nm, raw_nms.nm
> > that reflect each record in nm_pairs with a std_nms_id = std_nms.id
> 
> SQLite version 3.8.11.1 2015-07-29 20:00:57
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite>
> sqlite> create table std_nms( id integer, nm text );
> sqlite> create table raw_nms( id integer, nm text );
> sqlite>
> sqlite> create table nm_pairs( id integer, std_nms_id integer,
> raw_nms_id integer );
> sqlite>
> sqlite> insert into std_nms( id, nm ) values( 1, 'std1' ),( 2, 'std2'
> ),( 3, 'std3' );
> sqlite> insert into raw_nms( id, nm ) values( 1, 'raw1' ),( 2, 'raw2'
> ),( 3, 'raw3' );
> sqlite>
> sqlite> insert into nm_pairs( id, std_nms_id, raw_nms_id ) values( 1,
> 1, 1 ),( 2, 2, 2 ),( 3, 3, 3 ),( 4, 1, 3 );
> 
> 
> sqlite> select sn.nm, rn.nm
> from std_nms sn
> inner join nm_pairs nmp on nmp.std_nms_id=sn.id
> inner join raw_nms rn on nmp.raw_nms_id=rn.id
> where sn.nm='std1';
> std1|raw1
> std1|raw3
> 
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 

[sqlite] help with query

[Simon Davies]

On 6 April 2016 at 12:22, e-mail mgbg25171  
wrote:
> Here are my tables specified as... tbl_nm | col1, col2...
> std_nms | id, nm
> raw_nms | id, nm
> nm_pairs | id, std_nms_id, raw_nms_id
>
> I'm wondering how to supply a single std_nms.nm and get back a list of pairs
> i.e. std_nm.nm, raw_nms.nm
> that reflect each record in nm_pairs with a std_nms_id = std_nms.id

SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> create table std_nms( id integer, nm text );
sqlite> create table raw_nms( id integer, nm text );
sqlite>
sqlite> create table nm_pairs( id integer, std_nms_id integer,
raw_nms_id integer );
sqlite>
sqlite> insert into std_nms( id, nm ) values( 1, 'std1' ),( 2, 'std2'
),( 3, 'std3' );
sqlite> insert into raw_nms( id, nm ) values( 1, 'raw1' ),( 2, 'raw2'
),( 3, 'raw3' );
sqlite>
sqlite> insert into nm_pairs( id,  std_nms_id, raw_nms_id ) values( 1,
1, 1 ),( 2, 2, 2 ),( 3, 3, 3 ),( 4, 1, 3 );


sqlite> select sn.nm, rn.nm
   from std_nms sn
   inner join nm_pairs nmp on nmp.std_nms_id=sn.id
   inner join raw_nms rn on nmp.raw_nms_id=rn.id
   where sn.nm='std1';
std1|raw1
std1|raw3

Regards,
Simon

[sqlite] help with query

[e-mail mgbg25171]

Here are my tables specified as... tbl_nm | col1, col2...
std_nms | id, nm
raw_nms | id, nm
nm_pairs | id, std_nms_id, raw_nms_id

I'm wondering how to supply a single std_nms.nm and get back a list of pairs
i.e. std_nm.nm, raw_nms.nm
that reflect each record in nm_pairs with a std_nms_id = std_nms.id

(of the record containing the supplied single std_nms.nm).
Thank you in anticipation.

Re: [sqlite] help with query

[snowbiwan]

Maybe something like this would work for you:

SELECT *
  FROM table
 WHERE data1 IN (SELECT data1
   FROM table
   GROUP BY data1
 HAVING count(*)>=3);

~snowbiwan



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/help-with-query-tp79978p79979.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help with query

[Keith Medcalf]

A correlated subquery:

select *
  from t
 where (select count(*)
  from t as b
 where b.data1 = t.data1) >= 3;

or with a subselected set of valid rows:

select *
  from t
 where data1 in (select data1
   from t as b
   group by data1
 having count(*) >= 3);


---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Hajo Locke
>Sent: Tuesday, 13 January, 2015 00:30
>To: sqlite-users@sqlite.org
>Subject: [sqlite] help with query
>
>Hello list,
>
>i have a problem finding right query, hope you can help me.
>I have a sample table like this:  http://pastebin.com/8qyBzdhH
>I want to select all lines where at least 3 lines in column data1 have
>same value.
>My expected result-set ist this: http://pastebin.com/UcaXLVx9
>How can this be done in one query?
>I tested with something like this:
>SELECT *,count(*) as mycount FROM `table` group by data1 having
>mycount>=3;
>But this results in summarized output, but i need every single line.
>I would need something like: select * from `table` where count(data1)>3;
>But this is not allowed.
>Do you have any hints for me?
>
>Thanks,
>Hajo
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help with query

[Hajo Locke]

Hello,

thanks a lot.  Works like a charm!
I should really do more sql.

Thanks,
Hajo


Am 13.01.2015 um 09:03 schrieb Hick Gunter:

Step 1: count the occurrences:

SELECT data1,count() AS count FROM table GROUP BY data1;

Step 2: get the rows with a count above the limit

SELECT data1,count() AS count FROM table GROUP BY data1 HAVING count >= 3;

Step 3: get the keys from the rows

SELECT data1 FROM (SELECT data1,count() AS count FROM table GROUP BY data1 HAVING 
count >= 3);

Step 4: retrieve the original rows

SELECT * FROM table WHERE data1 IN (SELECT data1 FROM (SELECT data1,count() AS 
count FROM table GROUP BY data1 HAVING count >= 3));

-Ursprüngliche Nachricht-
Von: Hajo Locke [mailto:hajo.lo...@gmx.de]
Gesendet: Dienstag, 13. Jänner 2015 08:30
An: sqlite-users@sqlite.org
Betreff: [sqlite] help with query

Hello list,

i have a problem finding right query, hope you can help me.
I have a sample table like this:  http://pastebin.com/8qyBzdhH I want to select 
all lines where at least 3 lines in column data1 have same value.
My expected result-set ist this: http://pastebin.com/UcaXLVx9 How can this be 
done in one query?
I tested with something like this:
SELECT *,count(*) as mycount FROM `table` group by data1 having mycount>=3; But 
this results in summarized output, but i need every single line.
I would need something like: select * from `table` where count(data1)>3; But 
this is not allowed.
Do you have any hints for me?

Thanks,
Hajo

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
  Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help with query

[Hick Gunter]

Step 1: count the occurrences:

SELECT data1,count() AS count FROM table GROUP BY data1;

Step 2: get the rows with a count above the limit

SELECT data1,count() AS count FROM table GROUP BY data1 HAVING count >= 3;

Step 3: get the keys from the rows

SELECT data1 FROM (SELECT data1,count() AS count FROM table GROUP BY data1 
HAVING count >= 3);

Step 4: retrieve the original rows

SELECT * FROM table WHERE data1 IN (SELECT data1 FROM (SELECT data1,count() AS 
count FROM table GROUP BY data1 HAVING count >= 3));

-Ursprüngliche Nachricht-
Von: Hajo Locke [mailto:hajo.lo...@gmx.de]
Gesendet: Dienstag, 13. Jänner 2015 08:30
An: sqlite-users@sqlite.org
Betreff: [sqlite] help with query

Hello list,

i have a problem finding right query, hope you can help me.
I have a sample table like this:  http://pastebin.com/8qyBzdhH I want to select 
all lines where at least 3 lines in column data1 have same value.
My expected result-set ist this: http://pastebin.com/UcaXLVx9 How can this be 
done in one query?
I tested with something like this:
SELECT *,count(*) as mycount FROM `table` group by data1 having mycount>=3; But 
this results in summarized output, but i need every single line.
I would need something like: select * from `table` where count(data1)>3; But 
this is not allowed.
Do you have any hints for me?

Thanks,
Hajo

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] help with query

[Hajo Locke]

Hello list,

i have a problem finding right query, hope you can help me.
I have a sample table like this:  http://pastebin.com/8qyBzdhH
I want to select all lines where at least 3 lines in column data1 have 
same value.

My expected result-set ist this: http://pastebin.com/UcaXLVx9
How can this be done in one query?
I tested with something like this:
SELECT *,count(*) as mycount FROM `table` group by data1 having mycount>=3;
But this results in summarized output, but i need every single line.
I would need something like: select * from `table` where count(data1)>3;
But this is not allowed.
Do you have any hints for me?

Thanks,
Hajo

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help forming query

[David M. Cotter]

> SELECT DISTINCT column FROM table WHERE column not NULL;

this is exactly what I needed, thanks!

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help forming query

[Simon Slavin]

On 18 Jun 2014, at 7:01am, David M. Cotter  wrote:

> also: if this query isn't *very* fast, then i'm fine with just "give me the 
> value of the first cell where there is data in that column"

SQL does not have a concept of 'first' row.  Rows in a table do not have any 
order.  You can retrieve the values, and then find a that row with that value 
that has the lowest rowid, but that's your own interpretation of 'first'.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help forming query

[Bradley Giesbrecht]

On Jun 17, 2014, at 11:01 PM, David M. Cotter  wrote:

> also: if this query isn't *very* fast, then i'm fine with just "give me the 
> value of the first cell where there is data in that column"
> 
> in the below case, i'd get a "1".  
> i would then proceed to delete everything with a "1" in it (from this and 
> other tables)
> then i'd ask again, this time i'd get a 4, repeat above
> then ask again, get nothing, and i'd be done
> 
> On Jun 17, 2014, at 10:54 PM, David M. Cotter  wrote:
> 
>> i have a table with a numeric column (not the key column)
>> i want to obtain from this table a list of unique numbers appearing in that 
>> one column
>> 
>> some cells in the column may have nothing, some may have duplicate numbers 
>> eg:
>> 
>>> 1
>>> 1
>>> 1
>>> 4
>>> _
>>> _
>>> 4
>>> _
>> 
>> note that "_" means "no data".  i want to get a list with [1, 4] as the 
>> result.  what is the proper SQLite query for this?


You want "distinct":
select distinct column from table where column is not null;

or "group by":
select column from table where column is not null group by column;


Regards,
Bradley Giesbrecht (pixilla)



signature.asc
Description: Message signed with OpenPGP using GPGMail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help forming query

[David Empson]

On 18/06/2014, at 5:54 pm, David M. Cotter  wrote:

> i have a table with a numeric column (not the key column)
> i want to obtain from this table a list of unique numbers appearing in that 
> one column
> 
> some cells in the column may have nothing, some may have duplicate numbers eg:
> 
>> 1
>> 1
>> 1
>> 4
>> _
>> _
>> 4
>> _
> 
> note that "_" means "no data". i want to get a list with [1, 4] as the 
> result.  what is the proper SQLite query for this?

SELECT DISTINCT column FROM table;

This will return a row for each unique value in table.column, with the values 
in no particular order.

Eliminating the "no data" entry can be done by checking the results, or if you 
want to eliminate it automatically you could use something like:

SELECT DISTINCT column FROM table WHERE column not NULL;

This assumes your "no data" is represented as NULL. If you have used something 
else to represent "no data" then you would need to compare against that.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help forming query

[David M. Cotter]

also: if this query isn't *very* fast, then i'm fine with just "give me the 
value of the first cell where there is data in that column"

in the below case, i'd get a "1".  
i would then proceed to delete everything with a "1" in it (from this and other 
tables)
then i'd ask again, this time i'd get a 4, repeat above
then ask again, get nothing, and i'd be done

On Jun 17, 2014, at 10:54 PM, David M. Cotter  wrote:

> i have a table with a numeric column (not the key column)
> i want to obtain from this table a list of unique numbers appearing in that 
> one column
> 
> some cells in the column may have nothing, some may have duplicate numbers eg:
> 
>> 1
>> 1
>> 1
>> 4
>> _
>> _
>> 4
>> _
> 
> note that "_" means "no data".  i want to get a list with [1, 4] as the 
> result.  what is the proper SQLite query for this?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help forming query

[David M. Cotter]

i have a table with a numeric column (not the key column)
i want to obtain from this table a list of unique numbers appearing in that one 
column

some cells in the column may have nothing, some may have duplicate numbers eg:

> 1
> 1
> 1
> 4
> _
> _
> 4
> _

note that "_" means "no data".  i want to get a list with [1, 4] as the result. 
 what is the proper SQLite query for this?

--
kJams:  Mac and Windows Karaoke: CD+G Rip, Mix & Burn!
Main:   http://kjams.com/wiki/
Downloads:  http://kjams.com/downloads/
What's New: http://kjams.com/history/
To Unsubscribe: Simply reply with "kJams: unsubscribe"

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with query

[Jeff Archer]

>From: Drake Wilson  Sun, November 14, 2010 7:50:19 AM
>> SELECT COUNT(Offset_Y) FROM (SELECT DISTINCT Offset_Y FROM Tiles WHERE 
>>PatternID 
>>
>> = 1);
>> 
>> Is it possible to have a single query that will generate a row for each 
>> PattenID, COUNT(Offset_Y) combination?
>
>Does SELECT PatternID, COUNT(DISTINCT Offset_Y) FROM Tiles GROUP BY
>PatternID do what you're looking for?

That's it.  Thank you.  I also joined in the pattern name.

CREATE VIEW [vwPatterns] AS 
SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, 
COUNT(DISTINCT Offset_X) AS Width, COUNT(DISTINCT Offset_Y) AS Height, 
Patterns.Description 

FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID  GROUP 
BY 
Tiles.PatternID;

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with query

[Drake Wilson]

Quoth Jeff Archer , on 2010-11-13 11:20:51 
-0800:
> And I can get the number of unique Y offsets in a pattern like so:
> 
> SELECT COUNT(Offset_Y) FROM (SELECT DISTINCT Offset_Y FROM Tiles WHERE 
> PatternID 
> = 1);
> 
> Is it possible to have a single query that will generate a row for each 
> PattenID, COUNT(Offset_Y) combination?

Does SELECT PatternID, COUNT(DISTINCT Offset_Y) FROM Tiles GROUP BY
PatternID do what you're looking for?

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help with query

[Jeff Archer]

I have a table

CREATE TABLE [Tiles] (
TileID    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
PatternID INTEGER NOT NULL REFERENCES [Patterns] DEFERRABLE INITIALLY 
DEFERRED,
Offset_X  REAL NOT NULL DEFAULT 0.0,
Offset_Y  REAL NOT NULL DEFAULT 0.0
);

And I can get the number of unique Y offsets in a pattern like so:

SELECT COUNT(Offset_Y) FROM (SELECT DISTINCT Offset_Y FROM Tiles WHERE 
PatternID 
= 1);

Is it possible to have a single query that will generate a row for each 
PattenID, COUNT(Offset_Y) combination?
i.e.
1 | 29
2 | 37
3 | 45

Thanks,
Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] HELP: prep'ed query ... LF function to return column_isNull??

[Rob Sciuk]

Jay,

I thank you for an inciteful and well considered position piece, and I 
respond in-line below ... (sorry for the length, but I found it difficult 
to excise much of the excellent description that you wrote).

On Mon, 10 Nov 2008, Jay A. Kreibich wrote:

[regretfully elided owing to space ...]

>  This might be where things are getting a bit confused.  Under the
>  Relational Model, "NULL" is not a value.  As you point out, it is not
>  inside any type domain.  In fact, the whole point of NULL is a special
>  "marker" of sorts that is specifically outside of any normal valid
>  value domain.
>
>  What exactly that marker means is open to academic debate (the "any
>  value" vs.  "unknown value" argument is a bit of a religious thing),
>  but from a theory standpoint it very accepted that NULL is not a
>  value in the traditional sense of an element inside a type domain.

Which harkens to my argument, to be able to *CONSISTENTLY* ascertain 
whether the datum contains a 'value', within the usable domain of the 
data type, and the corresponding sqlite3_column_xxx() function will return 
it, or it does not (eg:  isNULL).  It is counter intuitive (to me) that 
the 'type' of a datum would transmogrify simply owing to the fact that it 
was not previously initialized, or indeed, was computed as an undefined 
operation (eg: div by zero).

Under normal circumstances, (ie: in other dbms systems, not SQLite) 
columns do not change type, and must defined within their domains, but 
within each domain, there exists a value which denotes a 'representation 
outside the domain'.  Some models do this better than others, but the
relational model only has NULL.

>
>  The actual SQL syntax is pretty good about recognizing this.  As
>  many a new SQL developer has found out (usually the hard way) SQL
>  doesn't like the idea of "...WHERE id = NULL...".  The SQL standard,
>  on the other hand, gets a bit sloppy with this.  Then again, so does
>  any text on the subject of SQL data manipulation.  It is very hard
>  not to.  If tasked with describing a statement like "UPDATE t set id
>  = NULL..." to a general audience, avoiding the use of the words
>  "NULL" and "value" in conjunction often makes for clumsy and
>  difficult to understand prose.  In my own writings on the subject
>  I've often found myself using phrases like "a NULL assignment", but
>  there are times when restructuring sentences to avoid the use of the
>  phrase "NULL value" is just too cumbersome to the rest of the flow.
>  That doesn't make it completely correct, however.
>
>  Writing style aside, I've found SQLite's handling of NULLs as a
>  unique data type with (essentially) and empty domain to be very
>  consistent.  In some ways, accepting this idea has allowed me to
>  adjust my mental model of SQL and relational data systems in a way
>  that's easier to accept NULL and all the weird syntax that goes with
>  it.  Something like the need for "...WHERE id IS NULL..." over
>  "WHERE id = NULL..." is a bit easier to accept if you think of NULL
>  as a type rather than a value.
>
>  Then again, that's within the context of SQLite's Manifest Typing--
>  something that is hugely useful, but about as far outside of "proper"
>  relational theory as you can get.  The Manifest Typing system already
>  requires SQLite to define all kinds of weird logic relations, like
>  sort orders for different type domains, as well as values within a
>  specific domain.  Adding one more type to the mix that has some
>  specific and unique interactions seems like a very clean way to deal
>  with the uniqueness of NULLs.
>
>> One would not query the
>> data type to determine whether the value of the datum is defined or not.
>
>  You would if the data container your asking about can hold anything from
>  a string to an integer.  While most of us tend to build databases where
>  all the values of a given column are of the same type (especially if
>  you've had prior database experience), SQLite doesn't require this.
>
>  So think of it as asking not so much the type, but the domain from
>  which this value came from, and if the datum is defined in that
>  domain.
>
>> Of course, SQLite has a unique affinity model, and I'm still wrestling
>> with this, though in my gut, I feel that along with the
>> sqlite3_column_xxx() functions, should be a sqlite3_column_isNULL()
>> function.
>
>  Once you get used to the Manifest Typing system, you'll realize that
>  something like "sqlite3_column_isNULL()" isn't really all that
>  different from something like "sqlite3_column_isBLOB()".  And from
>  that point, you're back to just asking for the "type" or domain of
>  the column with the existing API.   Chances are, you're just not used
>  to asking questions like "is this a BLOB?" because you've never had
>  to deal with a database that might offer different answers to that
>  question for the same column.

Arguable, yes.  The thing about the 'blob' which makes it special, is that 
the onus 

Re: [sqlite] HELP: prep'ed query ... LF function to return column_isNull??

[Jay A. Kreibich]

On Sat, Nov 08, 2008 at 09:48:10PM -0500, Rob Sciuk scratched on the wall:

> Thanks, Igor, but I'm not looking for the type (either declared or 
> affined), but rather whether the actual value is valid, or NULL.  That was 
> the point of the original post.  My problems arose from the fact that it 
> is the sqlite3_column_type() function which returns the fact that the 
> current value is NULL, and this is somewhat counterintuitive.
> 
> In my books, the data type is the type, and the data value is either NULL, 
> or has a value in the domain of that data type. 

  This might be where things are getting a bit confused.  Under the
  Relational Model, "NULL" is not a value.  As you point out, it is not
  inside any type domain.  In fact, the whole point of NULL is a special
  "marker" of sorts that is specifically outside of any normal valid
  value domain.
  
  What exactly that marker means is open to academic debate (the "any
  value" vs.  "unknown value" argument is a bit of a religious thing),
  but from a theory standpoint it very accepted that NULL is not a
  value in the traditional sense of an element inside a type domain.

  The actual SQL syntax is pretty good about recognizing this.  As
  many a new SQL developer has found out (usually the hard way) SQL
  doesn't like the idea of "...WHERE id = NULL...".  The SQL standard,
  on the other hand, gets a bit sloppy with this.  Then again, so does
  any text on the subject of SQL data manipulation.  It is very hard
  not to.  If tasked with describing a statement like "UPDATE t set id
  = NULL..." to a general audience, avoiding the use of the words
  "NULL" and "value" in conjunction often makes for clumsy and
  difficult to understand prose.  In my own writings on the subject
  I've often found myself using phrases like "a NULL assignment", but
  there are times when restructuring sentences to avoid the use of the
  phrase "NULL value" is just too cumbersome to the rest of the flow.
  That doesn't make it completely correct, however.

  Writing style aside, I've found SQLite's handling of NULLs as a
  unique data type with (essentially) and empty domain to be very
  consistent.  In some ways, accepting this idea has allowed me to
  adjust my mental model of SQL and relational data systems in a way
  that's easier to accept NULL and all the weird syntax that goes with
  it.  Something like the need for "...WHERE id IS NULL..." over
  "WHERE id = NULL..." is a bit easier to accept if you think of NULL
  as a type rather than a value.

  Then again, that's within the context of SQLite's Manifest Typing--
  something that is hugely useful, but about as far outside of "proper"
  relational theory as you can get.  The Manifest Typing system already
  requires SQLite to define all kinds of weird logic relations, like
  sort orders for different type domains, as well as values within a
  specific domain.  Adding one more type to the mix that has some
  specific and unique interactions seems like a very clean way to deal
  with the uniqueness of NULLs.

> One would not query the 
> data type to determine whether the value of the datum is defined or not. 

  You would if the data container your asking about can hold anything from
  a string to an integer.  While most of us tend to build databases where
  all the values of a given column are of the same type (especially if
  you've had prior database experience), SQLite doesn't require this.

  So think of it as asking not so much the type, but the domain from
  which this value came from, and if the datum is defined in that
  domain.

> Of course, SQLite has a unique affinity model, and I'm still wrestling 
> with this, though in my gut, I feel that along with the 
> sqlite3_column_xxx() functions, should be a sqlite3_column_isNULL() 
> function.

  Once you get used to the Manifest Typing system, you'll realize that
  something like "sqlite3_column_isNULL()" isn't really all that
  different from something like "sqlite3_column_isBLOB()".  And from
  that point, you're back to just asking for the "type" or domain of
  the column with the existing API.   Chances are, you're just not used
  to asking questions like "is this a BLOB?" because you've never had
  to deal with a database that might offer different answers to that
  question for the same column.

  That's not good or bad... I've got a lot of different and sometimes
  conflicting opinions on Manifest Typing, although I have to admit
  that, at the end of the day, I think it is a positive point for SQLite and
  the market it is trying to serve.  But that's a different can of worms.





  Most of this is related to the mental model each of us has about how
  SQL, relational systems, and our specific database needs fit
  together.  I'm not trying to say your own model is "wrong", or that
  the SQLite way is the best way.  I'm only trying to show that if you
  see things from a specific viewpoint, they're at least consistent.

   -j

-- 
Jay A. Kreibich 

Re: [sqlite] HELP: prep'ed query ... LF function to return column_isNull??

[Rob Sciuk]

> From: "Igor Tandetnik" <[EMAIL PROTECTED]>
> > "Rob Sciuk" <[EMAIL PROTECTED]> wrote in
> >
> > The other bit of trickery involved is that in order for the value
> > types to be correct (meaningful), you have to take a "step" as it
> > were ... with sqlite3_step 8-).
> 
> You can use sqlite3_column_decltype[16] without calling sqlite3_step
> first. This gives you the "syntactic" type, the type that can be
> inferred from the text of the statement and the database schema.
> 
> Igor Tandetnik

Thanks, Igor, but I'm not looking for the type (either declared or 
affined), but rather whether the actual value is valid, or NULL.  That was 
the point of the original post.  My problems arose from the fact that it 
is the sqlite3_column_type() function which returns the fact that the 
current value is NULL, and this is somewhat counterintuitive.

In my books, the data type is the type, and the data value is either NULL, 
or has a value in the domain of that data type.  One would not query the 
data type to determine whether the value of the datum is defined or not. 
Of course, SQLite has a unique affinity model, and I'm still wrestling 
with this, though in my gut, I feel that along with the 
sqlite3_column_xxx() functions, should be a sqlite3_column_isNULL() 
function.

Indeed, in a former life I have had a great deal of experience with 
SIR/DBMS, which is able to differentiate between undefined, NULL and up to 
three user defined MISSING VALUES per data item, and moreover to 
differentiate amoung them.  Of course, SIR/DBMS was not a general purpose 
database, and specialized in scientific applications, and particularly 
statistical analysis where such subtlety was important.

So, again, to those who offered advice, thanks, and I'm quite ok with the 
way things work, now that I know how to proceed -- I'm just saying ... 
8-).

Cheers,
Rob Sciuk

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] HELP: prep'ed query ... LF function to return column_isNull??

[Igor Tandetnik]

"Rob Sciuk" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> The other bit of trickery involved is that in order for the value
> types to be correct (meaningful), you have to take a "step" as it
> were ... with sqlite3_step 8-).

You can use sqlite3_column_decltype[16] without calling sqlite3_step 
first. This gives you the "syntactic" type, the type that can be 
inferred from the text of the statement and the database schema.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] HELP: prep'ed query ... LF function to return column_isNull??

[Rob Sciuk]

On Sat, 8 Nov 2008, Simon Davies wrote:
>
> Hi Rob,
>
> I haven't tried this, but sqlite3_column_type(sqlite3_stmt*, int iCol)
> should do what you want:
> http://www.sqlite.org/c3ref/column_blob.html
>
> Rgds,
> Simon

Thanks, Simon.

I found this out about two minutes after I'd posted the query.

At first, I figured that to be an awkward approach, as I felt that the 
column_isNULL function should follow the 'get data' functions (type vs 
value), but given SQLite's affinity model, and upon reflection, I suppose 
that it is the correct approach, even though slightly non-intuitive.

The other bit of trickery involved is that in order for the value types to 
be correct (meaningful), you have to take a "step" as it were ... with 
sqlite3_step 8-).  In any event, I'm all sorted out, and I appreciate your 
very quick and accurate response.

Cheers,
Rob Sciuk

PS:  I've said it before, and at risk of repetition, thanks and kudos to 
DRH for what is clearly one of the most remarkable examples of FOSS 
*EVER*.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] HELP: prep'ed query ... LF function to return column_isNull??

[Simon Davies]

2008/11/7 Rob Sciuk <[EMAIL PROTECTED]>:
>
> I don't see in the documentation a function to return whether or not the
> database value returned by the sqlite3_step() function is NULL.  Surely
> there should be such a beast, no?
>
>  Something like:
>int sqlite3_column_isNull( stmt, i ) ;
> or even:
>int sqlite3_column_isDefined( stmt, i ) ;
>
>
> This should work across all datatypes, and simply allow proper null
> handling -- not relying upon affinity dependant conversions ... hopefully,
> I've simply overlooked something really obvious, but at this moment, is
> opaque to me ... any pointers??
>
> Cheers,
> Rob Sciuk
>

Hi Rob,

I haven't tried this, but sqlite3_column_type(sqlite3_stmt*, int iCol)
should do what you want:
http://www.sqlite.org/c3ref/column_blob.html

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] HELP: prep'ed query ... LF function to return column_isNull??

[Rob Sciuk]

I don't see in the documentation a function to return whether or not the 
database value returned by the sqlite3_step() function is NULL.  Surely 
there should be such a beast, no?

  Something like:
int sqlite3_column_isNull( stmt, i ) ;
 or even:
int sqlite3_column_isDefined( stmt, i ) ;


This should work across all datatypes, and simply allow proper null 
handling -- not relying upon affinity dependant conversions ... hopefully, 
I've simply overlooked something really obvious, but at this moment, is 
opaque to me ... any pointers??

Cheers,
Rob Sciuk

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with query

[P Kishor]

On 4/8/08, Neville Franks <[EMAIL PROTECTED]> wrote:
> Thanks Igor and Puneet,
>  These are very different solutions, or so it appears to me.
>
>  Any idea whether the join or the sub-select would be faster? In my
>  example there is an index on tagid.

Just as in any language, SQL also gives many ways to say the same thing.

I am basically finding all the rows for 'a' first, and then from that
smaller set, finding all the rows for 'b'. If you index your table on
tagid, the query should fly.

Igor's query is throttling at the JOIN level by creating a smaller set
to select from and then applying the constraint on that smaller set.

If you are not dealing with large datasets, go with either one. As
long as you are getting what you want, how does it matter how you get
there? If performance is a consideration, benchmark it.

Fwiw, Igor is a 4 time winner of the world SQL championships, and has
brought home the gold medal on many occasions. In comparison, I am
still shelving tattered SQL tutorials at the local public library. I
would trust Igor over me.

>
>
>
>  Wednesday, April 9, 2008, 8:12:53 AM, you wrote:
>
>
>  IT> Neville Franks <[EMAIL PROTECTED]> wrote:
>  >> I have a table that holds 1 to many items. To keep it simple say it
>  >> has 2 columns: tagid and noteid. A given tagid can have many noteid's.
>  >> ex.
>  >> tagid   noteid
>  >> --  --
>  >> a   1
>  >> a   4
>  >> a   7
>  >> b   7
>  >> b   3
>  >> c   1
>  >>
>  >> I want to perform a query: give me all noteid's that have tagid a and
>  >> tagid b.
>
>
> IT> select t1.noteid
>  IT> from tableName t1 join tableName t2 on (t1.noteid = t2.noteid)
>  IT> where t1.tagid='a' and t2.tagid='b';
>
>  IT> Igor Tandetnik
>
>
>
>  IT> ___
>  IT> sqlite-users mailing list
>  IT> sqlite-users@sqlite.org
>  IT> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
>  --
>  Best regards,
>   Neville Franks, http://www.surfulater.com http://blog.surfulater.com
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with query

[Neville Franks]

Thanks Igor and Puneet,
These are very different solutions, or so it appears to me.

Any idea whether the join or the sub-select would be faster? In my
example there is an index on tagid.



Wednesday, April 9, 2008, 8:12:53 AM, you wrote:

IT> Neville Franks <[EMAIL PROTECTED]> wrote:
>> I have a table that holds 1 to many items. To keep it simple say it
>> has 2 columns: tagid and noteid. A given tagid can have many noteid's.
>> ex.
>> tagid   noteid
>> --  --
>> a   1
>> a   4
>> a   7
>> b   7
>> b   3
>> c   1
>>
>> I want to perform a query: give me all noteid's that have tagid a and
>> tagid b.

IT> select t1.noteid
IT> from tableName t1 join tableName t2 on (t1.noteid = t2.noteid)
IT> where t1.tagid='a' and t2.tagid='b';

IT> Igor Tandetnik 



IT> ___
IT> sqlite-users mailing list
IT> sqlite-users@sqlite.org
IT> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with query

[Igor Tandetnik]

Neville Franks <[EMAIL PROTECTED]> wrote:
> I have a table that holds 1 to many items. To keep it simple say it
> has 2 columns: tagid and noteid. A given tagid can have many noteid's.
> ex.
> tagid   noteid
> --  --
> a   1
> a   4
> a   7
> b   7
> b   3
> c   1
>
> I want to perform a query: give me all noteid's that have tagid a and
> tagid b.

select t1.noteid
from tableName t1 join tableName t2 on (t1.noteid = t2.noteid)
where t1.tagid='a' and t2.tagid='b';

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with query

[P Kishor]

On 4/8/08, Neville Franks <[EMAIL PROTECTED]> wrote:
> I have a table that holds 1 to many items. To keep it simple say it
>  has 2 columns: tagid and noteid. A given tagid can have many noteid's.
>  ex.
>  tagid   noteid
>  --  --
>  a   1
>  a   4
>  a   7
>  b   7
>  b   3
>  c   1
>
>  I want to perform a query: give me all noteid's that have tagid a and
>  tagid b. The result for the example above would be:
>
>  noteid
>  --
>  7
>


select noteid from t where tagid = 'b' and noteid in (select noteid
from t where tagid = 'a');

>  Can someone point me in the right direction. I am also interested in
>  optimal performance here.
>
>  I'm new to SQL and so far I am just doing simple select's. Thanks.
>
>  --
>  Best regards,
>   Neville Franks, http://www.surfulater.com http://blog.surfulater.com
>
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help with query

[Neville Franks]

I have a table that holds 1 to many items. To keep it simple say it
has 2 columns: tagid and noteid. A given tagid can have many noteid's.
ex.
tagid   noteid
--  --
a   1
a   4
a   7
b   7
b   3
c   1

I want to perform a query: give me all noteid's that have tagid a and
tagid b. The result for the example above would be:

noteid
--
7

Can someone point me in the right direction. I am also interested in
optimal performance here.

I'm new to SQL and so far I am just doing simple select's. Thanks.

-- 
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with query

[Brian Pugh]

Fred,

A query I can understand!

This was my attempt, and worked to some degree, but yours is more workable
and, I feel, more accurate

"select
NewsData.Postcode,NewsData.Address1,Agents.Code,Agents.Shopname,Agents.Addre
ss1.Agents.Price from NewsData join Agents on NewsData.Postcode where
NewsData.Shop1>'' or NewsData.Shop2>'' order by NewsData.Postcode asc"

Thanks very much for your interest and help,

Brian

Re: [sqlite] Help with query

[Fred Bleuzet]

Without testing on my side... how's that?

select a.Shop1, a.Shop2, a.Postcode, b.Code, b.Shopname, b.Address1, b.Price
from NewsData a, Agents b
where (a.Shop1 > '' or a.Shop2 > '') and b.Shopname > '' and (a.Shop1
= b.Shopname or a.Shop2 = b.Shopname)
order by a.Postcode asc


On Fri, 8 Oct 2004 18:27:59 +0100, Brian Pugh
<[EMAIL PROTECTED]> wrote:
> Hi,
> 
> This query, "select distinct Shop1,Shop2,Postcode from NewsData where Shop1>'' or 
> Shop2>'' order by Postcode asc"
> works as I require, but I want to expand the query to include data from another 
> table, and have got a bit stuck
> 
> The second table is called Agents. If the above query is successful, I want it to 
> include the columns called
> Code, Shopname, Address1 and Price, but only if the Shopname column in the Agents 
> table matches either
> Shop1 or Shop2 in the NewsData table
> 
> Can anyone help me with this?
> 
> Regards,
> 
> Brian Pugh
> 
>

[sqlite] Help with query

[Brian Pugh]

Hi,

This query, "select distinct Shop1,Shop2,Postcode from NewsData where Shop1>'' or 
Shop2>'' order by Postcode asc"
works as I require, but I want to expand the query to include data from another table, 
and have got a bit stuck

The second table is called Agents. If the above query is successful, I want it to 
include the columns called
Code, Shopname, Address1 and Price, but only if the Shopname column in the Agents 
table matches either
Shop1 or Shop2 in the NewsData table

Can anyone help me with this?

Regards,

Brian Pugh