Querying two tables help?

[Oatley]

Hello, hope you are all well

I have a question. On my website I have a search facility, and when a user enters their search term it can either be interpreted as a category (held in my categories table) or a name of a title of a story (held in my stories table).

Now in the stories table each story belongs to a category from the categories table, which is storied as a foreign key in the stories table.

Now say the term entered is "racing" on my website for example, what I do is this currently is

(1) run a like query on the categories table to find all the catid's where term like '%racing%' to hold those id values in an array to use them in my later query like so

Select catid from categories where name like '%racing%'

(2) Then run a search on my stories table to find all the stories where the term is like '%racing%' as well as imploding on the results of the previous query like so

Select * from stories where name like '%racing%' OR catid IN ('".implode("','",$array)."')

Then echo the results of this query on my page.

So currently I run two queries, which works OK, but I'd love to get it into one query. Is this possible?

Thank You

[guelphdad]

See the manual for JOINs.

[Old Pedant]

SELECT stories.give, stories.list, stories.of, stories.fields, categories.wanted
FROM stories INNER JOIN categories
ON stories.catid = categories.catid
WHERE categories.name LIKE '%racing%'
  AND stories.name LIKE '%racint%'

Or use an "implicit join", thus:

SELECT stories.give, stories.list, stories.of, stories.fields, categories.wanted
FROM stories, categories
WHERE stories.catid = categories.catid
  AND categories.name LIKE '%racing%'
  AND stories.name LIKE '%racint%'

And/or use aliases on your table names to make shorter queries, thus:

SELECT S.give, S.list, S.of, S.fields, C.wanted
FROM stories AS S, categories AS C
WHERE S.catid = C.catid
  AND C.name LIKE '%racing%'
  AND S.name LIKE '%racint%'

Or or or or ...