Oh – all the sql stuff has never been real easy for me at all. Basically I
have a database which is filled by several applications working with tags.
In the database there is only one table that contains the names of the tags
– the other one links to the application and specifies in which content the
tag occurred.
However, after the
http://www.nabble.com/forum/ViewPost.jtp?post=23474516&framed=y hint from P
Kishor I waited about four minutes and the freeze was gone. My query seems
to work but it takes much to long. Currently the table 'tag_name' has 6690
and 'tag_link' 50714 entries.
Your JOIN-query felt like one second – but only with one COUNT :/
Kees Nuyt wrote:
>
> On Sun, 10 May 2009 15:09:01 -0700 (PDT), S Fiedler
> <ste.fied...@googlemail.com> wrote:
>
>>
>>Hi Kees,
>>
>>thanks for your help. Thats a neater way than I structured my JOIN version
>>before. But my goal is to have all tag COUNTs for each application in one
>>result row + id and name of the tag. Like:
>>
>>tag-id | tag-name | count_app_t | count_app_d
>>-----------------------------------------------------
>>1 | sql | 9 | 2
>>2 | xml | 61 | 0
>>3 | foo | 47 | 826
>>
>>Until now no 'JOIN construction' allowed more than one COUNT. Thats why I
>>tried out the SELECT (SELECT …), (SELECT) way – which didn't though
errors,
>>but produced the freeze of the script.
>
> Aha, I see, you mean a pivot report.
> That can't be easily done in plain SQL.
>
> What is weird in your example, is that the same application
> ('d') uses the same tag more then once. Is that on purpose?
> In other words, it's not completely clear to me what you are
> trying to accomplish.
>
>>Regards,
>>-steffen
>>
>>
>>Kees Nuyt wrote:
>>>
>>> On Sun, 10 May 2009 13:51:29 -0700 (PDT), ckeen
>>> <ste.fied...@googlemail.com> wrote:
>>>
>>>>
>>>>Hi, I'm trying to count two different column combinations using two
>>tables.
>>>>Table one (tag_name) contains the 'id' and the 'name' for a tag. Table
two
>>>>is stuffed with entries that links those tags with different
applications
>>>>and their contents.
>>>>Now I would like to select how often each tag is used in each
application.
>>>>Some of my previous tries worked fine (using JOIN ON) – but only one
COUNT
>>>>was possible. Now I'm trying to get a statement to work which returns me
>>the
>>>>tag amounts for both apps.
>>>>
>>>>tag_name
>>>>id | tag
>>>>--------
>>>>1 | sql
>>>>2 | xml
>>>>3 | foo
>>>>
>>>>tag_link
>>>>id | app | app_id | tag_id
>>>>----------------------------
>>>>1 | d | 331 | 2
>>>>2 | t | 49 | 1
>>>>
>>>>Here is my current statement:
>>>>SELECT
>>>> tag_name.id,
>>>> (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND
>>>>tag_link.app = 't') AS cntTwt,
>>>> (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND
>>>>tag_link.app = 'd') AS cntDel
>>>>FROM
>>>> tag_name
>>>>GROUP BY
>>>> tag_name.id
>>>
>>>
>>> sqlite_version():3.6.13
>>> CREATE TABLE tag_name (
>>> id INTEGER PRIMARY KEY,
>>> tag TEXT
>>> );
>>> CREATE TABLE app_name (
>>> id INTEGER PRIMARY KEY,
>>> app TEXT
>>> );
>>> CREATE TABLE tag_link (
>>> app_id INTEGER,
>>> tag_id INTEGER,
>>> PRIMARY KEY (app_id,tag_id)
>>> );
>>> INSERT INTO tag_name values (1,'sql');
>>> INSERT INTO tag_name values (2,'xml');
>>> INSERT INTO tag_name values (3,'foo');
>>>
>>> INSERT INTO app_name values (30,'a');
>>> INSERT INTO app_name values (39,'b');
>>> INSERT INTO app_name values (49,'t');
>>> INSERT INTO app_name values (331,'d');
>>>
>>> INSERT INTO tag_link values (331,1);
>>> INSERT INTO tag_link values (331,2);
>>> INSERT INTO tag_link values (49,1);
>>> INSERT INTO tag_link values (30,1);
>>> INSERT INTO tag_link values (39,2);
>>> INSERT INTO tag_link values (331,3);
>>> INSERT INTO tag_link values (49,3);
>>>
>>> SELECT app_name.id,app,COUNT(tag_link.tag_id) AS nrtags
>>> FROM tag_link
>>> INNER JOIN app_name ON (app_name.id = tag_link.app_id)
>>> GROUP BY tag_link.app_id;
>>>
>>> app_name.id|app_name.app|nrtags
>>> 30|a|1
>>> 39|b|1
>>> 49|t|2
>>> 331|d|3
>>>
>>>
>>>>The parser returns no error, only seems to freeze. Tipps, hints – all
kind
>>>>of advice.
>>>>sincerely, ckeen
>>>
>>> Hope this helps.
> --
> ( Kees Nuyt
> )
> c[_]
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
--
View this message in context:
http://www.nabble.com/Multiple-counts-between-two-tables-tp23473911p23474904.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
