Unanswered: Need help on database multiple search criteria
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!
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.