Thanks Petite Abeille, I translated your code to this:
SELECT genres.genres,
(
SELECT substr(group_concat(name,' '),1,60)
FROM
(
SELECTname
FROM movies
JOIN genres
ON
Petite Abeille-2 wrote
> Sure. But your conclusion is a most likely a red herring. The crux of the
> matter is that inner and outer join have a wildly different semantic. And
> therefore execution plan. That's all. Seems all very reasonable from an
> optimizer point of view.
But I have no
Thanks but 4 rows is not what I am looking for.
I found a solution, concatenation:
SELECT genres.genres,
(
SELECT group_concat(movie,' ')
FROM
(
SELECTmovies.movies||','||name movie
FROM movies
JOIN
Ok, wonderful, now it is working correctly but how do I select multiple
columns from table movies?
Should I add another sub query?
Example:
SELECT genres.genres,
(
SELECT substr(group_concat(name,' '),1,60)
FROM
(
SELECTname
I did some testing and found some strange results.
SELECT genres FROM genres LEFT JOIN movies ON genres.movies = movies.movies
GROUP BY genres ORDER BY genres;
time: 2.475s
SELECT genres FROM genres JOIN movies ON genres.movies = movies.movies GROUP
BY genres ORDER BY genres;
time: 0.035s
I did an ANALYZE but I'm getting the same results.
I do have indexes:
CREATE TABLE movies (
movies UNIQUE,
name,
icon_modified
);
CREATE TABLE genres (
genres,
movies,
UNIQUE(genres,movies)
);
people has an index on people (people UNIQUE) and tasks is a view:
CREATE VIEW tasks AS
Thanks Igor but now it is taking even more time: 3.139s
Any other idea?
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71003.html
Sent from the SQLite mailing list archive at Nabble.com.
I have 2 tables:
CREATE TABLE movies (
movies UNIQUE,
name,
icon_modified
);
CREATE TABLE genres (
genres,
movies,
);
I want to construct genres icons and each icon must display 4 movies.
I need to get: genres from genres and movies,icon_modified from movies.
This is the query I have:
Thank you very much, that worked perfectly, I have one more question.
Given the same schema, I can list all genres with one movie by genre:
SELECT genres, movies FROM genres GROUP BY genres;
Action|[movie id]
Adventure|[movie id]
Animation|[movie id]
Biography|[movie id]
Comedy|[movie id]
Hello, I have the following tables:
CREATE TABLE movies (movies,name);
CREATE TABLE genres (movies,genres);
Every movie has many genres and every genre has many movies.
I want to list all genres but those who match a specified movie must be
marked.
For example for movie "tt1637725":
SELECT
BareFeetWare-2 wrote:
>
> Oh, I see, so the "complication" is that you would have to change the
> columns used in your bash script. That shouldn't be a big problem, but
> I'll leave the bash script to you.
>
It isn't a big problem, the "complication" was to adapt all the tables and
inserts and
BareFeetWare-2 wrote:
>
> Does IMDB allow use of their data this way? After my brief reading of
> their site, I thought they charge a $15k minimum per year for data.
>
IMDb Pro offers additional information and services, all the information I
am grabbing is already available to the public and
BareFeetWare-2 wrote:
>
> What is your source for the data? If I have that, I can test my schema
> properly, rather than sitting here entering a pile of dummy data. I looked
> at IMDB, but they only seem to have a paid license download.
>
I am grabbing the data from the each movie imdb
BareFeetWare-2 wrote:
>
> Oh, I see. I was counting the total number of each unique capacity
> (including director, writers), but you want the number of each unique
> (capacity, person). No problem. We'll just add a People column to the
> statistics, and change the uniqueness constraint.
>
>
Simon Slavin-3 wrote:
>
> By looking at the file on disk ? Are you taking into account the journal
> file ?
>
Yes, I do all the counts and save the data to a file and then if the file is
newer then the database I use the file else I count again.
No, I am not taking the journal file into
BareFeetWare-2 wrote:
>
> In that case, you should cache the counts in a separate table or two. That
> has a negligible overhead when you add a movie (which is infrequent), and
> basically no overhead when viewing (which is frequent).
I am doing that but in the application level, the down side
Simon Slavin-3 wrote:
>
> How much slower. Did you make an index SQLite could use for that query ?
>
Using distinct isn't slow, it is what I would expect but count(*) is
incredibly fast, it is instantaneous no matter how large is the table.
Yes, I tried with an index.
BareFeetWare-2 wrote:
BareFeetWare-2 wrote:
>
> Then you can count the directors like this:
>
> select count(distinct People_ID) from "Movie People" join Capacity on
> "Movie People".Capacity_ID = Capacity.ID where Capacity.Name = 'director';
>
> or:
>
> select count(distinct People_ID) from "Movie People" where
Hi, thanks a lot for the helpful replies.
Sorry to bother again, but there is still something that hasn't been
answered.
Simon Slavin-3 has addressed my question but not exactly what I was asking.
Suppose I have two tables "movies" and "people" and other tables to relate
both tables:
Ok, thanks all for your answers, I guest I will have to normalize the
database and explore the foreign key feature.
One more thing:
I have the tables "movies" and "people"
Those two tables are related by tables "directors", "writers", etc...
movies: id, title
people: id, name
directors:
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
Black, Michael (IS) wrote:
>
> Test#1
> create index tags_index on tags(tag);
> You should have an index for any fields you query on like this.
>
Thanks Michael but I don't see any speed improvement:
create index test on tags(tag);
select count(distinct tag) from tags;
This is much faster:
Hi.
I have this tables:
CREATE TABLE movies(movie_id INTEGER, title TEXT, unique(movie_id));
CREATE TABLE tags(movie_id INTEGER, tag TEXT, unique(movie_id,tag));
I have many movies by tag and many tables like "tags" (keywords, countries,
languages, genres, etc..)
I can count different movies
Thanks for the answer.
I have another problem:
I decided to use a view to do this, I will have to drop/create the view
every time filters is updated but I don't know how to use multiple selects.
I want to do something like this:
CREATE VIEW movies_filters AS SELECT 'rated',movie FROM movies
Hello,
Suppose I have this tables:
movies: movie,name,rating
tags: movie,tag
filters: filter,expression
In filters I have:
rated,rating != ''
tagged,movie in (select movie from tags)
Now I want to create a view to relate movies and filters exactly like the
tags table:
movies_filters:
Tim Romano wrote:
>
> You should keep your id and the imdbid in separate columns, because you
> can then insert a title even if IMDB does not have it yet.
>
Ok, I have thought about this and it will be probably simpler to have two
separated ids than using a trigger.
Now, there are some
CREATE TABLE movies(id integer,title text,unique(id))
Generally I have an unique id from imdb but some times the movie isn't
available.
I understand that there is a column called rowid that is the primary key.
I would like to insert the rowid on the id column when I don't have an id
for the
Is this a bug in new versions that will be fixed or it is the new behavior?
--
View this message in context:
http://old.nabble.com/Multiples-natural-left-joins-problem-tp26627140p2730.html
Sent from the SQLite mailing list archive at Nabble.com.
> You have not specified your version, but I can repeat your
> difficulties when using version 3.6.19 and 3.6.20
>
I am using version 3.6.16
> You can get 'correct' results if you modify your query to
> SELECT movies.id, title, files, icon_modified, icon_width
> FROM ( movies natural left
> What query are you trying with the above tables, and how do the
> results differ from what you expect?
>
This is the query:
SELECT movies.id,title,files,icon_modified,icon_width
FROM (movies natural left join files) natural left join icons_movies
LIMIT 25 ;
The difference with your last
SimonDavies wrote:
>
> sqlite> create table movies( id integer primary key, title text );
> sqlite> insert into movies( title ) values( 'movie1' );
> sqlite> insert into movies( title ) values( 'movie2' );
> sqlite> insert into movies( title ) values( 'movie3' );
> sqlite>
> sqlite> create
Thanks both for the replies.
Pavel Ivanov-2 wrote:
>
> If using "natural left join" is not a requirement for you then this
> works as you expect it:
> select movies.id, title, rating, tag
> from movies left join user on movies.id = user.id
> left join tag on movies.id = tag.id;
>
Ok,
Lets say I have the following tables:
movies: id,title
user: id,rating
tag: id,tag
I want to left join all three tables:
SELECT movies.id,movies.title,user.rating,tag.tag
FROM movies natural left join user natural left join tag
The problem with this besides having to specify the
Ok, I have replaced the older version and now I have the same sqlite version
on both computers but the problem persist. Changes don't get saved on the
computer that access the database trough the network.
Any idea?
--
View this message in context:
I have one database on a shared folder across the network.
The database is accessed by two different computers with different sqlite
versions.
There was no problem until I updated the OS on one of the computer.
Now I have:
1: Ubuntu Hardy - sqlite3 3.4.2
2: Ubuntu Karmic - sqlite3 3.6.16
The
I have this:
select title,my_rating
from movies left join user on movies.id = user.id
where id = 'tt0426459'
The result:
ambiguous column name: id
I could use:
select movies.id ids,title,my_rating
from movies left join user on movies.id = user.id
where ids =
Jim Wilcoxson wrote:
>
> For example, in your query you need to add something like:
> and genres.genre = 'drama'.
Yes but if I add that I neither get any result:
sqlite3 movies.db "select movies.id,title,year from
movies,genres,countries,languages,keywords,tags where movies.id = genres.id
and
P Kishor-3 wrote:
> Google for "normalizing a database" and then read up on it. It will help.
>
Ok, thanks, thats the solution. Now there is another problem that arise.
Lets say I have a table called movies with 3 columns (id, titles, keywords):
1|title1|keyword1
1|title1|keyword2
Thanks for the answer.
Igor Tandetnik wrote:
> This monstrosity gives the correct answer in your specific example, but
> it relies on there being exactly two tags per folder.
It can be any number of tags per folder.
Igor Tandetnik wrote:
>
> Consider normalizing your database. Split into two
Suppose that have 2 columns: folders and tags:
~/Music|classic,rock
~/Music|classic,rock
~/Pictures|art,photos
~/Pictures|art,photos
~/Pictures|art,photos
To know the folder count I do:
sqlite3 test.db "select folder, count(folders) from t1 group by folder"
Returns:
~/Music|2
~/Pictures|3
How
40 matches
Mail list logo
