Re: [sqlite] How do I write this query

[James K. Lowden]

On Mon, 29 Apr 2013 16:14:00 -0500
"Michael Black"  wrote:

> sqlite> CREATE TABLE foo( a integer, b integer);
> sqlite> INSERT INTO foo VALUES(1,1);
> sqlite> INSERT INTO foo VALUES(1,2);
> sqlite> INSERT INTO foo VALUES(1,3);

With 3.7.13 at least, 

insert into foo values (1,1),(1,2),(1,3);

works. 

HTH.

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

Re: [sqlite] How do I write this query

[Igor Korot]

Thx.

On Mon, Apr 29, 2013 at 2:14 PM, Michael Black <mdblac...@yahoo.com> wrote:

> sqlite> CREATE TABLE foo( a integer, b integer);
> sqlite> INSERT INTO foo VALUES(1,1);
> sqlite> INSERT INTO foo VALUES(1,2);
> sqlite> INSERT INTO foo VALUES(1,3);
> sqlite> INSERT INTO foo VALUES(2,1);
> sqlite> INSERT INTO foo VALUES(2,2);
> sqlite> INSERT INTO foo VALUES(2,3);
> sqlite> CREATE TABLE bar( a integer, b integer, c integer);
> sqlite> INSERT INTO bar SELECT a,b,1 FROM foo;
> sqlite> select * from bar;
> 1|1|1
> 1|2|1
> 1|3|1
> 2|1|1
> 2|2|1
> 2|3|1
>
> Mike
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Korot
> Sent: Monday, April 29, 2013 4:06 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] How do I write this query
>
> Hi, ALL,
>
> CREATE TABLE foo( a integer, b integer);
> INSERT INTO foo VALUES( 1,1);
> INSERT INTO foo VALUES( 1,2);
> INSERT INTO foo VALUES( 1,3);
> INSERT INTO foo VALUES( 2,1);
> INSERT INTO foo VALUES( 2,2);
> INSERT INTO foo VALUES( 2,3);
>
> CREATE TABLE bar( a integer, b integer, c integer);
>
> INSERT INTO bar VALUES((SELECT a, b FROM foo),1); // fails
> INSERT INTO bar( a, b ) VALUES( (SELECT a, b FROM foo) ); //fails
>
> What is the correct syntax?
>
> Basically I need bar to have records from foo with field c to be 1, so:
>
> SELECT * FROM bar;
> 1 1 1
> 1 2 1
> 1 3 1
> 2 1 1
> 2 2 1
> 2 3 1
>
> Thank you.
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I write this query

[Michael Black]

sqlite> CREATE TABLE foo( a integer, b integer);
sqlite> INSERT INTO foo VALUES(1,1);
sqlite> INSERT INTO foo VALUES(1,2);
sqlite> INSERT INTO foo VALUES(1,3);
sqlite> INSERT INTO foo VALUES(2,1);
sqlite> INSERT INTO foo VALUES(2,2);
sqlite> INSERT INTO foo VALUES(2,3);
sqlite> CREATE TABLE bar( a integer, b integer, c integer);
sqlite> INSERT INTO bar SELECT a,b,1 FROM foo;
sqlite> select * from bar;
1|1|1
1|2|1
1|3|1
2|1|1
2|2|1
2|3|1

Mike

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Korot
Sent: Monday, April 29, 2013 4:06 PM
To: General Discussion of SQLite Database
Subject: [sqlite] How do I write this query

Hi, ALL,

CREATE TABLE foo( a integer, b integer);
INSERT INTO foo VALUES( 1,1);
INSERT INTO foo VALUES( 1,2);
INSERT INTO foo VALUES( 1,3);
INSERT INTO foo VALUES( 2,1);
INSERT INTO foo VALUES( 2,2);
INSERT INTO foo VALUES( 2,3);

CREATE TABLE bar( a integer, b integer, c integer);

INSERT INTO bar VALUES((SELECT a, b FROM foo),1); // fails
INSERT INTO bar( a, b ) VALUES( (SELECT a, b FROM foo) ); //fails

What is the correct syntax?

Basically I need bar to have records from foo with field c to be 1, so:

SELECT * FROM bar;
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1
2 3 1

Thank you.
___
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] How do I write this query

[markus diersbock]

Why are basic SQL questions being asked in the SQLite forum?



On Mon, Apr 29, 2013 at 5:06 PM, Igor Korot  wrote:
> Hi, ALL,
>
> CREATE TABLE foo( a integer, b integer);
> INSERT INTO foo VALUES( 1,1);
> INSERT INTO foo VALUES( 1,2);
> INSERT INTO foo VALUES( 1,3);
> INSERT INTO foo VALUES( 2,1);
> INSERT INTO foo VALUES( 2,2);
> INSERT INTO foo VALUES( 2,3);
>
> CREATE TABLE bar( a integer, b integer, c integer);
>
> INSERT INTO bar VALUES((SELECT a, b FROM foo),1); // fails
> INSERT INTO bar( a, b ) VALUES( (SELECT a, b FROM foo) ); //fails
>
> What is the correct syntax?
>
> Basically I need bar to have records from foo with field c to be 1, so:
>
> SELECT * FROM bar;
> 1 1 1
> 1 2 1
> 1 3 1
> 2 1 1
> 2 2 1
> 2 3 1
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 

Markus Diersbock
SwingNote
Marblehead, MA

C 781.929.0693
F 888.654.6068

This e-mail is intended solely for the person or entity to which it is
addressed and may contain confidential information. If you have
received this e-mail in error, please contact the sender immediately
and delete the material from any computer. Any review, dissemination,
copying, printing or other use of this e-mail by persons or entities
other than the addressee is prohibited.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How do I write this query

[Igor Korot]

Hi, ALL,

CREATE TABLE foo( a integer, b integer);
INSERT INTO foo VALUES( 1,1);
INSERT INTO foo VALUES( 1,2);
INSERT INTO foo VALUES( 1,3);
INSERT INTO foo VALUES( 2,1);
INSERT INTO foo VALUES( 2,2);
INSERT INTO foo VALUES( 2,3);

CREATE TABLE bar( a integer, b integer, c integer);

INSERT INTO bar VALUES((SELECT a, b FROM foo),1); // fails
INSERT INTO bar( a, b ) VALUES( (SELECT a, b FROM foo) ); //fails

What is the correct syntax?

Basically I need bar to have records from foo with field c to be 1, so:

SELECT * FROM bar;
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1
2 3 1

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

Re: [sqlite] How do I write a query

[Igor Korot]

Hi, Simon,

On Sat, Mar 23, 2013 at 10:22 PM, Simon Slavin  wrote:
>
> On 24 Mar 2013, at 5:03am, Igor Korot  wrote:
>
>> When application starts I need to retrieve first all available players
>> and then all players that are drafted.
>>
>> Probably the easiest way would be to query playersinleague for all
>> players and then query draftedplayers for all drafted players.
>
> That way should work.  And your schema looks good too apart from SQLite 
> having no 'double' type.  I recommend you store prices as integers instead.
>
>> But is it efficient? Is it fastest?
>
> Don't worry about either 'efficient' or 'fastest'.  Just worry about 'fast 
> enough'.  Generally speaking the best way to design your program is whatever 
> most resembles how you thought about solving the problem.  If the program 
> works the way you think, it'll be easier to program and easier to debug.
>
> If you write it, and it's annoyingly slow to use, then yes, go into heavy 
> hacking mode and program for 10 hours to save 2 seconds runtime.  But until 
> then it's wasted effort and hard to document.  In real life, most programs 
> spend most of their time waiting for humans to do something.  You can speed 
> something up by .84 seconds and nobody will ever notice.
>
>> I will probably need an index created on draftedplayers on the leagueid, 
>> right?
>
> Right.  When making indexes do not think about "What columns would be good to 
> index."  Instead think about "What kind of index would best suit that query 
> ?".  So perhaps wait until you know more about the SELECT you'll be doing, 
> because you might find that it has an ORDER BY clause you can put into the 
> same index and speed it up even more.

What do you mean by "put ORDER BY clause in the index"?
Could you clarify?

Thank you.

>
> Simon.
> ___
> 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] How do I write a query

[Igor Tandetnik]

On 3/25/2013 3:59 PM, Igor Korot wrote:

What do you mean by "put ORDER BY clause in the index"?
Could you clarify?


For example, if you often need to run a query like "select * from T 
where A = ? order by B;", it would benefit from an index on T(A, B).

--
Igor Tandetnik

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

Re: [sqlite] How do I write a query

[Petite Abeille]

On Mar 24, 2013, at 8:30 PM, Larry Brasfield  wrote:

>> That said… it seems to be odd to have a 'player' vs . 'draftedplayers' 
>> table… looks like a typical is_a vs has_a confusion...
> 
> The table named 'draftedplayers' is simply a many-to-many relation between 
> the 'player' and 'league' tables.  It lacks attributes of 'player' entries 
> other than the primary key.  I see no is_a versus has_a confusion here.

Right you are. Sloppy reading.

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

Re: [sqlite] How do I write a query

[Larry Brasfield]

Petite Abeille wrote:

That said… it seems to be odd to have a 'player' vs . 'draftedplayers' table… 
looks like a typical is_a vs has_a confusion...


The table named 'draftedplayers' is simply a many-to-many relation 
between the 'player' and 'league' tables.  It lacks attributes of 
'player' entries other than the primary key.  I see no is_a versus has_a 
confusion here.


Cheers,
--
Larry Brasfield

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

Re: [sqlite] How do I write a query

[Petite Abeille]

On Mar 24, 2013, at 4:13 PM, Simon Slavin  wrote:

> SELECT playerid FROM players WHERE playerid IS NOT IN (SELECT playerid FROM 
> draftedplayers)

Alternatively:

select  playerid
fromplayers

where   not exists
(
  select  1
  fromdraftedplayers

  where   draftedplayers.playerid = players.playerid
)

Or simply:

select  playerid
from  players

except
select  playerid
from  draftedplayers


That said… it seems to be odd to have a 'player' vs . 'draftedplayers' table… 
looks like a typical is_a vs has_a confusion...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I write a query

[Simon Slavin]

On 24 Mar 2013, at 7:23am, Igor Korot  wrote:

> Basically what I wanted to ask was: how do I write a query to retrieve
> non-drafted players in the league.

Well, you have a table of players, and a table of drafted players.

> Then I can get the drafted players and it will eliminate a search
> algorithm to draft players at start.
> I think this solution will work even better, don't you?
> 
> Will this query work:
> 
> SELECT playerid, name FROM players, league, playersinleague,
> drafrtedplayers WHERE players.playerid = playersinleague,playersid AND
> league.leagueid = playersinleague.leagueid AND league.leagueid =
> draftedplayers.leagueid AND draftedplayers.playerid is NULL AND
> league.leagueid = 1;

You seem to have implied some sort of JOIN ON clause in the above without 
actually stating it.  I don't know whether that's legal in SQLite.

There are many ways to do it.  Some depend on how you are using your 
draftedplayers table.  If each player only ever has 0 or 1 entry in it, in 
other words if it reflects your /current/ drafts only, then you could just 
merge it into your playerid table, and have leagueid and draftprice be NULL if 
a player isn't drafted yet.

Another way would be to use a JOIN to find the undrafted players ...

SELECT playerid FROM players JOIN draftedplayers ON draftedplayers.playerid = 
players.playerid WHERE draftedplayers.playerid IS NULL

(or use IS NOT NULL at the end).  I think that'll work.  Another way is to 
collect a list of drafted players and see which ones are in it:

SELECT playerid FROM players WHERE playerid IS NOT IN (SELECT playerid FROM 
draftedplayers)

I doubt any of these will work exactly as I wrote them but they should give you 
ideas for syntax.  Also I don't know how fantasy football works so I probably 
misunderstand your league system.  But you have plenty of experimentation from 
the above.

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

Re: [sqlite] How do I write a query

[Igor Korot]

Hi, Simon,

On Sat, Mar 23, 2013 at 10:22 PM, Simon Slavin  wrote:
>
> On 24 Mar 2013, at 5:03am, Igor Korot  wrote:
>
>> When application starts I need to retrieve first all available players
>> and then all players that are drafted.
>>
>> Probably the easiest way would be to query playersinleague for all
>> players and then query draftedplayers for all drafted players.
>
> That way should work.  And your schema looks good too apart from SQLite 
> having no 'double' type.  I recommend you store prices as integers instead.

I don't know what happened. I guess the stars was standing this way or
something... ;-)

Basically what I wanted to ask was: how do I write a query to retrieve
non-drafted players in the league.
Then I can get the drafted players and it will eliminate a search
algorithm to draft players at start.
I think this solution will work even better, don't you?

Will this query work:

SELECT playerid, name FROM players, league, playersinleague,
drafrtedplayers WHERE players.playerid = playersinleague,playersid AND
league.leagueid = playersinleague.leagueid AND league.leagueid =
draftedplayers.leagueid AND draftedplayers.playerid is NULL AND
league.leagueid = 1;

Thank you.
>
>> But is it efficient? Is it fastest?
>
> Don't worry about either 'efficient' or 'fastest'.  Just worry about 'fast 
> enough'.  Generally speaking the best way to design your program is whatever 
> most resembles how you thought about solving the problem.  If the program 
> works the way you think, it'll be easier to program and easier to debug.
>
> If you write it, and it's annoyingly slow to use, then yes, go into heavy 
> hacking mode and program for 10 hours to save 2 seconds runtime.  But until 
> then it's wasted effort and hard to document.  In real life, most programs 
> spend most of their time waiting for humans to do something.  You can speed 
> something up by .84 seconds and nobody will ever notice.
>
>> I will probably need an index created on draftedplayers on the leagueid, 
>> right?
>
> Right.  When making indexes do not think about "What columns would be good to 
> index."  Instead think about "What kind of index would best suit that query 
> ?".  So perhaps wait until you know more about the SELECT you'll be doing, 
> because you might find that it has an ORDER BY clause you can put into the 
> same index and speed it up even more.
>
> Simon.
> ___
> 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] How do I write a query

[Simon Slavin]

On 24 Mar 2013, at 5:03am, Igor Korot  wrote:

> When application starts I need to retrieve first all available players
> and then all players that are drafted.
> 
> Probably the easiest way would be to query playersinleague for all
> players and then query draftedplayers for all drafted players.

That way should work.  And your schema looks good too apart from SQLite having 
no 'double' type.  I recommend you store prices as integers instead.

> But is it efficient? Is it fastest?

Don't worry about either 'efficient' or 'fastest'.  Just worry about 'fast 
enough'.  Generally speaking the best way to design your program is whatever 
most resembles how you thought about solving the problem.  If the program works 
the way you think, it'll be easier to program and easier to debug.

If you write it, and it's annoyingly slow to use, then yes, go into heavy 
hacking mode and program for 10 hours to save 2 seconds runtime.  But until 
then it's wasted effort and hard to document.  In real life, most programs 
spend most of their time waiting for humans to do something.  You can speed 
something up by .84 seconds and nobody will ever notice.

> I will probably need an index created on draftedplayers on the leagueid, 
> right?

Right.  When making indexes do not think about "What columns would be good to 
index."  Instead think about "What kind of index would best suit that query ?". 
 So perhaps wait until you know more about the SELECT you'll be doing, because 
you might find that it has an ORDER BY clause you can put into the same index 
and speed it up even more.

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

[sqlite] How do I write a query

[Igor Korot]

Hi, ALL,
CREATE TABLE players( playerid integer primary key, name );
CREATE TABLE league( leagueid integer primary key, name );
CREATE TABLE playersinleague( playerid integer, leagueid integer,
price double, foreign key playerid references players(playerid),
foreign key leagueid references league(leagueid) );
CREATE TABLE draftedplayers( playerid integer, leagueid integer,
draftprice double, foreign key playerid references players(playerid),
foreign key leagueid references league(leagueid) );

I have a pool of players which is assigned to a league by some
conditions. Different leagues might have different players.
Now from the playersinleague I am drafting players one by one.
In the end of my application I will store the drafted players into the
draftedplayers table.

When application starts I need to retrieve first all available players
and then all players that are drafted.

Probably the easiest way would be to query playersinleague for all
players and then query draftedplayers for all drafted players.
But is it efficient? Is it fastest?

I will probably need an index created on draftedplayers on the leagueid, right?

Thank you for any suggestions.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users