SQL Help - How to query for something that contains more than one keyword?

[GeForceX]

Hey guys,

A little help: I'm having an issue with trying to retrieve a result from a query. Perhaps the table relationship is designed wrong. Maybe I'm using the wrong SQL commands. I'm very new to databases and SQL so I hope you can forgive me for this.

[B]Article Table[/B] - [I]Attrib: Article ID, Article Title[/I]
 |
<> [B]Article_Keyword Table[/B] - [I]Attrib: Article ID, Keyword ID [/I]
 |
[B]Keyword Table[/B] - [I]Attrib: Keyword ID, Keyword Description[/I]

An article can have many keywords.
A keyword can belong to many articles.

A user can search for an article with more than 1 keyword, for example:
I want to search for an article that has keyword 1 (science) and keyword 5 (medical).

I tried to use this SQL statement:

SELECT Article ID
FROM Article_Keyword Table
WHERE Keyword_ID = '1' and '5'

But I know this does not work. I've been trying to figure out how to get multiple keywords in a single query.

Another thought was to use a subquery using something like this:

SELECT Article ID
FROM Article_Keyword Table
WHERE Keyword_ID = '1' IN
(SELECT Article ID
FROM Article_Keyword Table
WHERE Keyword_ID = '5')

But of course, that isn't the correct SQL statement.

Any help?

Thanks.

 

[Jonnyy]

Wouldn't it just be

SELECT Article ID
FROM Article_Keyword
WHERE Keyword_ID IN (1, 5)

 

[mikeblas]

Are you trying to find articles that have both keyword 1 and keyword 5 in the same article? The query Jonnyy gives shows articles that have keyword 1, as well as articles that have keyword 5, and also articles that have both keywords.

 

[Arainach]

How about:

SELECT id FROM table WHERE
id IN (SELECT id FROM table WHERE keyword = 1) AND
id IN (SELECT id FROM table WHERE keyword = 5)

?

 

[mikeblas]

That would give IDs of articles with both keyword 1 and keyword 5, but probably isn't the most efficient way to code the query.

 

[GeForceX]

Are you trying to find articles that have both keyword 1 and keyword 5 in the same article?

What you have said is actually what I want - getting articles that contain both keywords (in the same article).

The query Jonnyy gives shows articles that have keyword 1, as well as articles that have keyword 5, and also articles that have both keywords.

I don't know if the query that Jonnyy offered would be useful because it would show 3 different results. I only want one result of articles containing both keywords only.

I am guessing Arainach's would work. But why would it not be efficient? Are you speaking in terms of performance, Mikeblas?