Author Topic: keyword search (not)  (Read 3622 times)

0 Members and 1 Guest are viewing this topic.

jaybird23238

  • Full Member
  • ***
  • Posts: 29
    • View Profile
keyword search (not)
« on: 2016-10-09 14:24:37 »
I need that ability to to search for files without a particular keyword, and it needs to tie in with an advance search.

Example:

File name mask: pet*.*
keyword not "dog"

Also need this to pair with an advance search and keyword not "dog"

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
keyword search (not)
« Reply #1 on: 2016-10-09 16:41:16 »
1) In human-readable format:
----------------------------

tn.idthumb not in (
   select distinct idthumb
   from thumbnailkeyword
   where thumbnailkeyword.idkeyword = (
      select idkeyword
      from keyword
      where keyword = 'dog'
   )
)


2) As a single long line, which should remain intact in the 'Found Files' results list, so that the query can be re-used later on:
----------------------------------------------------------------------

tn.idthumb not in (select distinct idthumb from thumbnailkeyword where
thumbnailkeyword.idkeyword = (select idkeyword from keyword where
keyword = 'dog'))
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

jaybird23238

  • Full Member
  • ***
  • Posts: 29
    • View Profile
keyword search (not)
« Reply #2 on: 2016-12-10 12:04:49 »
> 1) In human-readable format:
> ----------------------------
>
> tn.idthumb not in (
>    select distinct idthumb
>    from thumbnailkeyword
>    where thumbnailkeyword.idkeyword = (
>       select idkeyword
>       from keyword
>       where keyword = 'dog'
>    )
> )
>
>
> 2) As a single long line, which should remain intact in the 'Found Files' results list, so that the query can be re-used later on:
> ----------------------------------------------------------------------
>
> tn.idthumb not in (select distinct idthumb from thumbnailkeyword where
> thumbnailkeyword.idkeyword = (select idkeyword from keyword where
> keyword = 'dog'))

How can I modify this to return all thumbnails that have no keywords at all.  (keyword - "")

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
keyword search (not)
« Reply #3 on: 2016-12-10 20:57:44 »
> How can I modify this to return all thumbnails that have no keywords at all.  

Try either of these queries:

tn.idthumb not in (select distinct idThumb from ThumbnailKeyword)

not exists (select idKeyword from ThumbnailKeyword where ThumbnailKeyword.idThumb = tn.idThumb)

not exists (select idthumb from thumbnailkeyword tk inner join keyword kw on tk.idkeyword = kw.idkeyword where kw.keyword = 'KEYWORD' and tk.idthumb = tn.idthumb)


Source: www.cerious.com/faq_sql.shtml
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.