mbrainz=# \d artistwords
Table "public.artistwords"
Column | Type | Modifiers
----------+---------+-----------
wordid | integer | not null
artistid | integer | not null
mbrainz=# \d artist
Table "public.artist"
Column | Type | Modifiers
-----------------+------------------------+-----------------------------------------------------
id | integer | not null default nextval('artist_id_seq'::regclass)
name | character varying(255) | not null
gid | character(36) | not null
modpending | integer | default 0
sortname | character varying(255) | not null
page | integer | not null
resolution | character varying(64) |
begindate | character(10) |
enddate | character(10) |
type | smallint |
quality | smallint | default -1
modpending_qual | integer | default 0
mbrainz=# \d wordlist
Table "public.wordlist"
Column | Type | Modifiers
----------------+------------------------+-------------------------------------------------------
id | integer | not null default nextval('wordlist_id_seq'::regclass)
word | character varying(255) | not null
artistusecount | smallint | not null default 0
albumusecount | smallint | not null default 0
trackusecount | smallint | not null default 0
labelusecount | smallint | not null default 0
Now I wanted to do a query on any number of words, match the word ids with the word, and then following match
the artist that has references to all those words. My attempt as as follows:
SELECT a.name AS artist_name
FROM artist a
WHERE NOT EXISTS
(
SELECT *
FROM artistwords aw
WHERE a.id = aw.artistid
AND
aw.wordid NOT IN
(
SELECT id from wordlist where (word = 'bob' OR word = 'dylan')
)
)
Unfortunately this only yields:
artist_name
Bob!
Bob
Bob
Bob
BOB
Dylan
bob.
Bob
bob
Bob
Which is fine, where it not that Bob Dylan is actually lacking; which I do not understand.