From: Bryan Tong Minh <bryan@tools.wikimedia.de>

Date: Sun, 28 Oct 2007 11:40:10

That would be something like

USE commonswiki_p;
SELECT CONCAT('* Image:', page_title) FROM image, page LEFT JOIN templatelinks ON tl_from = page_id WHERE (page_namespace, page_title) = (6, img_name) AND (tl_namespace, tl_title) <> (10, 'Information') AND tl_from IS NULL;

It would be much easier if all Commons license tags would embed Category:License_tags aside from the sub cats of License_tags.

From: Dan Collins <EN.WP.ST47@gmail.com>

Date: Sun, 28 Oct 2007 11:42:00

I just did this for en - let's see if I can do it here too.

From: Dan Collins <EN.WP.ST47@gmail.com>

Date: Sun, 28 Oct 2007 13:06:39

select page_title from page, image where page_namespace = 6 and page_title=img_name and page_id not in (select tl_from from templatelinks);

select img_name from image where img_name not in (select page_title from page where page_namespace = 6 );

and results are at [[commons:User:ST47/NT]]

From: Daniel Kinzler <daniel@brightbyte.de>

Date: Sun, 28 Oct 2007 15:11:56

For future reference: where ... not in (select ...) is rather slow if you do it on a lot of entries. rather use left join ... where x is null.

Anyway, the above query will only find pages without any template. there's a lot of non-license templates. I have built a list of license templates, and a view that is like templatelinks, but only for license templates - that would give a more accurate result I hope.

Oh well, maybe next time ![][1] hm, perhaps i should make this stuff available somewhere, somehow. maybe create a commonswiki_aux_p database containing "auxiliary" data?

[1]: https://jira.toolserver.org/images/icons/emoticons/smile.gif

This bug was imported as RESOLVED. The original assignee has therefore not been
set, and the original reporters/responders have not been added as CC, to
prevent bugspam.

If you re-open this bug, please consider adding these people to the CC list:
Original assignee: EN.WP.ST47@gmail.com
CC list: koneko@tiscali.fr, Bryan.TongMinh@Gmail.com, daniel.kinzler@wikimedia.de, EN.WP.ST47@gmail.com