Hi everyone,

I am new to databases and I am needing some help doing a particular query I am not sure how to code.

I have three tables:

user: with fields id, name, login, password

user_expertise: with fields id, name

questions: with fields id, user_id, expertise_id


A user can be an expert on multiple areas. Therefore, a user defined in the user table has many entries in the user_expertise table.

A user can have multiple questions. This way, a user defined in the user table can have many questions stored in the questions table by user_id and expertise_id he needs help from.

I need to do a query that selects all the entries from the questions table that match all the expertise areas defined for a user.

I know how to do a query for one expertise area for a user in the questions table, however I do not know how to create a query that iterates through all the expertise areas or values defined for a user on the user_expertise table in order to get all the entries that match that set of multiple values in questions table.

Thanks in advance and please forgive my bad english!

You need to do a left join on all three tables.

Do you know how to join two tables?

Do you know the difference between INNER and OUTER joins?

Take a look at the manual if you don't and then try a join on at least two of the tables, if not all three.

Remember to preface your id columns with the table names like this:
user.id or you will get "field name is ambigous" errors which means if you use just id it doesn't know which of the three you are referring to.

After you show some work here someone will help you with the three table join.

Oh, are you on MySQL 4.1 above so you can take advantage of subqueries and group_concat functions should they be necessary? Or if you are on an older version let us know when you post any threads.

Thanks a lot! I really appreciate your answer. I feel so ignorant about all this stuff. Learning MySQL in one day is not the way to go.

So far I have been able to get the results I want with a query like this one:

SELECT questions.expertise_id FROM questions, user, user_expertise WHERE user.id=$user_id AND user_expertise.user_id = $user_id AND questions.expertise_id = user_expertise.expertise_id

Where $user_id is a php variable on my script.

Is this what you would call a three-way-joint?

I will post the database soon. This was kind of emergency question!