SQL syntax help?? Multiple conditions for same field?

[spook007]

I have a table that is made up of 2 fields on a MySQL DB. ID and KEYWORD. KEYWORD AND ID both make up the PRIMARY KEY. I am trying to write an SQL statment that will allow me to retrieve the ID that has both 'annual' and 'report' in the table.

Sample table would look like this.

ID KEYWORD
2 annual
4 annual
2 report

With the above info I should retrieve the ID value of 2. Since 2 is the only ID that has 'annual' and 'report'.

I've tried this SQL statement, but it doesn't return any records.

SELECT * KEYWORD
WHERE (keyword like "%annual% and
keyword like "%report%&quot

I think the problem with the above syntax is that is is looking for the words 'annual reports' on a single field. This will not happen since the words annual and report are on different records.

Any suggestions would be greatfully appreciated.

 

[sleipnir214]

You're right. Your where clause required keyword to contain both "report" and "annual".

Try this one:

select distinct f.id from foo f, foo b where f.keyword like '%report%' and b.keyword like '%annual%'

 

[danielhozac]

I think the problem with your query is that you use and instead of or. Try changing it to or and it should work. //Daniel