从多个表中选择然后加入

Question

I'm new here and I hope I am asking my question correctly: I am trying to implement search on forums database. I have 'questions' table and 'answers' table (they both related by 'id_question' field). I also have 'suggestions' table and 'comments' table that are also related. In addition I have 'profiles' table which related to every table I've mentioned before by 'profile_id' field, this profiles table holds the information on the users.

I would like to implement wide search on specific fields ('topic', 'description'...) in all the four tables I've mentioned before, and display those fields and the information of the user who wrote the post. I really messed with this. right now I have 4 queries (for each table), and each query makes join with profiles table. Do you know better way to do this?

Thanks!

Answer 1

You can do this with INNER JOINS. I recommend starting with some simple INNER JOINS and building up your queries to be more inclusive and join more tables. It all depends on what you're trying to get, really. Be as precise as possible in returning exactly the information you want.

Here is a simple example and the assumptions I made:

Assuming the following table: primarykey*, foreignkey#, othercolumns

questions: id_questions*, topic, description, profile_id#
answers: id_answers*, id_questions#, topic, description, profile_id#
suggestions: id_suggestions*, topic, description, profile_id#
comments: id_comments*, topic, description, profile_id#
user: profile_id*, name, details

KEYWORD: replace with your keyword or phrase

Look for a keyword in the questions and answers

SELECT * FROM questions q
INNER JOIN answers a
ON q.profile_id = a.profile_id
WHERE topic LIKE '%KEYWORD%'
OR description LIKE '%KEYWORD%';

Get the user profile for a user who used keyword in the questions and answers tables:

SELECT u.* FROM users u
INNER JOIN answers a
ON u.profile_id = a.profile_id
INNER JOIN questions q
ON q.profile_id = u.profile_id
WHERE topic LIKE '%KEYWORD%'
OR description LIKE '%KEYWORD%';

Hope this helps.

Edit: formatting.