Re: [sqlite] complex query

[Josh Marell]

I would say that couple should be a 3 column table, with coupleID,
partner1ID, partner2ID.  It looks like right now, you have just coupleID and
partnerID, that doubles the number of rows you have.

On Thu, Feb 24, 2011 at 7:02 AM, Igor Tandetnik  wrote:

> Aric Bills  wrote:
> > Using the results of this query, I'd like to identify cousins.  It should
> be
> > possible to define two people as cousins if they share a grandparent but
> > have no parents in common.  Defining the query above as the view
> > "grandparentage", I can come up with a query for people who share a
> common
> > grandparent as follows:
> >
> >SELECT
> >p.personid,
> >c.personid AS cousinid,
> >FROM
> >grandparentage AS p,
> >grandparentage AS c
> >WHERE
> >p.grandparentid = c.grandparentid
> >
> > What I'm not sure how to do is determine whether p.personid has any
> parents
> > in common with c.personid.
>
> and not exists (
>select 1 from parentage parent1, parentage parent2
>where parent1.parentid = parent2.parentid
>and parent1.personid = p.personid
>and parent2.personid = c.personid)
>
> --
> Igor Tandetnik
>
> ___
> 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] Speed up count(distinct col)

[Josh Marell]

I would create a tagList table (integer tagID, string tagName,
unique(tagName))

Before performing your batch of inserts, query the tagList table (integer
tagID, string tagName), and generate a map (key on tagName, value of tagID).
For each tag you want to insert, see if it exists in the map.  If it
doesn't, insert first into tagList table and get the tagID of that new entry
and update your map.  Perform your insert with the tagID instead now.  If it
does exist, use the value of the tagName key in your map.

I chose a map (or some similar implementation), because the lookup should be
quick, and you won't need to query the DB for each new tag during your batch
of inserts.

On Mon, Feb 7, 2011 at 2:32 PM, Yuzem  wrote:

>
>
> Petite Abeille-2 wrote:
> >
> > Hey... sounds like IMdb :P
> >
> Yes, I'm coding a  http://yuzem.blogspot.com/p/figuritas-screenshots.html
> movie manager  that grabs the info from imdb.
>
>
> Petite Abeille-2 wrote:
> >
> > In any case, as mentioned by Michael Black, you might benefit greatly by
> > normalizing your table and indexing the foreign key:
> >
> > http://dev.alt.textdrive.com/browser/IMDB/IMDB.ddl#L401
> >
>
> I thought sqlite didn't handle foreign keys correctly, I am using triggers
> to automatically delete tags when a movie gets deleted.
> If I use a foreign key will it automatically remove the tag if the movie
> gets deleted?
> Anyway, to use integers in the "tags" table is very complicated because I
> will have to assign the corresponding number to each tag that I insert and
> I
> have to insert lots of keywords for every movie.
> Does sqlite has any function to convert a text string into an unique
> number?
>
> --
> View this message in context:
> http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30867411.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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] View with values in 1st table replaced by values in second table

[Josh Marell]

Thank you both for the advice, I had never thought to join on the same table
using 3 different names like that, will have to keep that in mind!

On Thu, Jan 13, 2011 at 9:27 PM, Jay A. Kreibich <j...@kreibi.ch> wrote:

> On Thu, Jan 13, 2011 at 01:44:12PM -0600, Josh Marell scratched on the
> wall:
>
> > Schedule {
> > date TEXT UNIQUE NOT NULL
> > problem_set INTEGER
> > literature INTEGER
> > research INTEGER}
> >
> > Presenters {
> > p_id INTEGER PRIMARY KEY
> > short_name TEXT UNIQUE NOT NULL}
>
> > I am trying to create a view such that the output is the 4 columns in the
> > schedule table, except instead of the p_id being displayed, I want to
> > replace those values with the short_name.
> >
> > For any given date, 2 of the 3 categories is set to a p_id and the 3rd is
> > null.
>
>
> CREATE VIEW Schedule_names AS
>  SELECT s.date, p.name, l.name, r.name
>FROM  Schedule   AS s
>LEFT JOIN Presenters AS p ON ( s.problem_set = p.p_id )
>LEFT JOIN Presenters AS l ON ( s.literature  = l.p_id )
>LEFT JOIN Presenters AS r ON ( s.research= r.p_id );
>
>
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> 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] View with values in 1st table replaced by values in second table

[Josh Marell]

Hi everyone.  I am new to this mailing list, so hopefully I will be able to
present my idea clearly to you:

I have two tables designed as:

Schedule {
date TEXT UNIQUE NOT NULL
problem_set INTEGER
literature INTEGER
research INTEGER}

Presenters {
p_id INTEGER PRIMARY KEY
short_name TEXT UNIQUE NOT NULL}

problem_set, literature, and research reference a p_id in the presenter
list.

I am trying to create a view such that the output is the 4 columns in the
schedule table, except instead of the p_id being displayed, I want to
replace those values with the short_name.

For any given date, 2 of the 3 categories is set to a p_id and the 3rd is
null.

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