self join/ select question
-
Hi, I have the following tables: Item (ItemID) and Item_Keyword(ItemID, KeywordID). ItemID is a primary key in the Item table. In the Item_Keyword table, both ItemID and KeywordID are foreign keys. I need to select such ItemID, that has ALL specified keywords. Thanks in advance for any idea/help
Sincerely, Elina Life is great!!! Enjoy every moment of it! :-O
-
Hi, I have the following tables: Item (ItemID) and Item_Keyword(ItemID, KeywordID). ItemID is a primary key in the Item table. In the Item_Keyword table, both ItemID and KeywordID are foreign keys. I need to select such ItemID, that has ALL specified keywords. Thanks in advance for any idea/help
Sincerely, Elina Life is great!!! Enjoy every moment of it! :-O
There might be a better way, but this will work: specified keywords=
'C++','C#','ASP'the where clause of the outer query will need to have theTotalWords = the number of specified keywords(3 in this case).select itemid from (select count(*) as TotalWords, itemid
from item_keyword ik inner join keyword k on ik.keywordid = k.keywordid
where k.keyword in(**'C++','C#','ASP'**)
group by itemid) as tbl
**where TotalWords = 3**--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
There might be a better way, but this will work: specified keywords=
'C++','C#','ASP'the where clause of the outer query will need to have theTotalWords = the number of specified keywords(3 in this case).select itemid from (select count(*) as TotalWords, itemid
from item_keyword ik inner join keyword k on ik.keywordid = k.keywordid
where k.keyword in(**'C++','C#','ASP'**)
group by itemid) as tbl
**where TotalWords = 3**--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
Thanks, I cam up with the similar solution. My concern is, that if there are 3 keywords, say
**'Java'**,'C#','ASP', this query will still select it, or wouldn't it?Sincerely, Elina Life is great!!! Enjoy every moment of it! :-O
-
Thanks, I cam up with the similar solution. My concern is, that if there are 3 keywords, say
**'Java'**,'C#','ASP', this query will still select it, or wouldn't it?Sincerely, Elina Life is great!!! Enjoy every moment of it! :-O
Elina Blank wrote:
My concern is, that if there are 3 keywords, say 'Java','C#','ASP', this query will still select it, or wouldn't it?
I don't understand the question. Please clarify.
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
Elina Blank wrote:
My concern is, that if there are 3 keywords, say 'Java','C#','ASP', this query will still select it, or wouldn't it?
I don't understand the question. Please clarify.
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
Sorry, you are absolutely right! It works, thanks a lot!
Sincerely, Elina Life is great!!! Enjoy every moment of it! :-O