I've recently started to try whip together a decent mysql query for a search page. The problem I have is I've been asked to include another tables data in query and I'm not exactly sure the best way to do it.
What I have is the following tables.
id, title, description
id, blog_id, textWhat i need to do is run the query string through
Its a one to many relationship with n amount of comments. BlogPostsTable.title, BlogPostsTable.description and BlogPostsCommentsTable.text and return * from the BlogPostsTable in order of highest rank.
Is this possible to do in one query? Only thing I can think of is 2 querys. One to check the comments and get ids and then a seperate to test the others eg title, description.
Biggest problem is ranking them after that. Or for search would you give priority to different items so if it find something in title that will come first, then description, then comments?
I hope this makes sense.
Thanks in advance