SQL / Relational Db Question. Nude content!

[dafuzz]

Sorry not nude, I meant nerd

I have a FileTable and a KeywordTable, and a FileKeywordTable to resolve the many-to-many relationship. The FileTable holds info on the FileID, Filename and Filetype (e.g text, powertab). The KeywordTable is just Keyword and KeywordID. The linker table is just FileID and KeywordID.

The first part of the SQL query goes:

SELECT DISTINCT FileTable.FileName 
FROM (FileKeywordTable INNER JOIN FileTable ON FileKeywordTable.FileID = FileTable.FileID) 
   INNER JOIN KeywordTable ON FileKeywordTable.KeywordID = KeywordTable.KeywordID 
WHERE

and the remainder (the WHERE clause) is built programmatically depending on user choices, but an example might be:

 (Keyword = 'thrash' AND 'metal') AND DocType = 'powertab'

While this query will return only power tabs, it will return any power tab that has the keyword 'metal' as well as any power tab that has the keyword 'thrash'. I want it to return only the docs that have both keywords 'thrash' and 'metal'. The brackets seem to make no difference but I've left them in anyway.

Does anybody have any idea what I've done wrong? I've been trawling teh web but no joy. Any help much appreciated

[Snap]

hmm, does using "&" or "+" instead of "AND" work?

[dafuzz]

Originally Posted by Snap

hmm, does using "&" or "+" instead of "AND" work?

<quick test>

Sadly no

Aren't they concatenation operators in some SQL flavours? I get no hits at all, which would make sense if its searching for 'thrashmetal'.

[roundthebend]

My first thought is that you might need to do a subquery.

I'll see what I can work out.

[arrowodd]

My sql programming was an age ago, and it's never really been of interest - so I could be way off mark here. in fact "inner join" is new to me.

(its in my head, but hard to explain)
It looks to me that by selecting from the intersection table [FileKeywordTable] which is word|file , however that 'and' works , you're only looking at individual keyword/file correspondancies and wont be able combine conditions.

I think some sort of selecting from filetable where exists [select from intersection: keyword = word1 and fileid joins] and exists [select from interstion: keyword = word2 and fileid joins] may be worth looking at.

[roundthebend]

SELECT F.FileName
FROM FileTable as F

where 'grunge' in
	(SELECT K.Keyword
	 FROM KeywordTable as K
	 INNER JOIN FileKeywordTable as FK ON FK.KeywordID = K.KeywordID
	 AND FK.FileID = F.FileID)


and 'lyrics' in
	(SELECT K.Keyword
	 FROM KeywordTable as K
	 INNER JOIN FileKeywordTable as FK ON FK.KeywordID = K.KeywordID
	 AND FK.FileID = F.FileID)

[roundthebend]

Although I think my query works, I think you should take arrowodd's use of the EXISTS keyword and rework it. I think it's a better practise for performance reasons.

[roundthebend]

use FileStoreTest
SELECT F.FileName
FROM FileTable as F

--where 'grunge' in
where EXISTS
	(SELECT K.Keyword
	 FROM KeywordTable as K
	 INNER JOIN FileKeywordTable as FK ON FK.KeywordID = K.KeywordID
	 AND FK.FileID = F.FileID
	 AND K.Keyword = 'grunge')


--and 'lyrics' in
and EXISTS
	(SELECT K.Keyword
	 FROM KeywordTable as K
	 INNER JOIN FileKeywordTable as FK ON FK.KeywordID = K.KeywordID
	 AND FK.FileID = F.FileID
	 AND K.Keyword = 'lyrics')

[dafuzz]

Thanks peeps! I'm just making the necessary changes now

It never ceases to amaze me the breadth and depth of knowledge on this forum. We should challenge the Eggheads

[Snap]

man, we really are a bunch of geeks. Concatentation - thought so in hindsight.

I have patchy knowledge......