Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: Querying two tables help?
02-14-2013, 03:51 PM #1
- Join Date
- Sep 2012
- Thanked 0 Times in 0 Posts
Querying two tables help?
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%'
Select * from stories where name like '%racing%' OR catid IN ('".implode("','",$array)."')
So currently I run two queries, which works OK, but I'd love to get it into one query. Is this possible?
02-14-2013, 04:56 PM #2
- Join Date
- Mar 2006
- St. Catharines, Ontario Canada
- Thanked 147 Times in 138 Posts
See the manual for JOINs.
02-14-2013, 08:03 PM #3
Or use an "implicit join", thus:Code: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%'
And/or use aliases on your table names to make shorter queries, thus:Code: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%'
Or or or or ...Code: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%'
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Users who have thanked Old Pedant for this post: