mysql 3 table join

select query involving 3 tables

[zeeshan_kh]

Hey,

I simply want to pull out result from three tables using join.

Table 1: users
user_id int
user_name varchar
-other fields

Table 2: users_profiles
users_profiles_id int
user_id int
question_id int
answer_id int

Table 3: users_matches_profiles
matches_profiles_id int
user_id int
question_id int
answer_id int

Every user answers some question to define his profile and then he answers same questions to define his match. questions and answers for his profile go in "users_profies" and questions and answers for his matches go in "users_profiles_matches"

I want to pull out all the users from users table where the questions and answers in "users_matches_profiles" match the questions and answers in "profiles_matches".

I am using this query but it pulls all the user in the database regardless of their questions and answers.

SELECT DISTINCT u.user_id, u.user_name
FROM users u, users_profiles up, users_matches_profiles ump
WHERE u.user_id <>18
AND ump.user_id =18
AND ump.question_id = up.question_id
AND ump.answer_id = up.answer_id

What i think this query should do it to pull all the users from the database where user_id is not equal to 18 but the questions and answers in the users_matches_profiles table against user_id 18 match the questions and answers in the profiles_matches table. I don't have alot of experience with database so i'm not surprised that the query is not working :)

Just wondering if someone else has worked on something like this before and can suggest a way to deal with it.

Thanks!

[syber]

It is best to use the JOIN syntax so that you don't inadvertenly cause a cartesian join:

SELECT DISTINCT u.user_id, u.user_name  
FROM users u  
 JOIN users_profiles up 
 ON u.user_id = up.user_id 
 JOIN users_matches_profiles ump  
 ON ump.question_id = up.question_id AND 
  ump.answer_id = up.answer_id  

Once you have the join working, you can then add whatever WHERE clause you need to restrict the rows.

SELECT DISTINCT u.user_id, u.user_name  
FROM users u  
 JOIN users_profiles up 
 ON u.user_id = up.user_id 
 JOIN users_matches_profiles ump  
 ON ump.question_id = up.question_id AND 
  ump.answer_id = up.answer_id  
WHERE u.user_id <>18 

[zeeshan_kh]

hey syber,

Thanks for your attention but unfortunately the query is not working in mysql. Its showing an error at 'on'.

Thanks!

[syber]

Ok, here it is then in the old style syntax

SELECT DISTINCT u.user_id, u.user_name  
 FROM users u, users_profiles up, users_matches_profiles ump  
 WHERE u.user_id <>18 AND 
 u.user_id = up.user_id AND 
 up.question_id = ump.question_id AND  
 up.answer_id = ump.answer_id  

[physics]

Not sure why that is giving an error but I think syber is right that the JOIN syntax is appropriate here, maybe LEFT JOIN ...

More on the join syntax here:
[dev.mysql.com...]

[FalseDawn]

You might have to bracket the individual JOINs:

SELECT DISTINCT u.user_id, u.user_name
FROM
((users u JOIN users_profiles up ON u.user_id = up.user_id)
JOIN users_matches_profiles ump
ON ump.question_id = up.question_id AND ump.answer_id = up.answer_id)

WHERE u.user_id <>18

[zeeshan_kh]

Hey folks,

Thanks for you replies. The query is till not working properly. It gives an error near 'ON' but if i change that 'JOIN' to 'LEFT JOIN' then the error fades away. But its still not giving the right result.

Now, i came up with this query today and it kinda brought me closer to the result i want.

SELECT distinct up.user_id, u.user_name
FROM users_matches_profiles ump, users_profiles up, users u
WHERE ump.user_id =18
AND (
ump.question_id = up.question_id
AND ump.answer_id = up.answer_id
)

AND (up.user_id = u.user_id AND u.user_id<>18)

What i think this should do is to pull all the rows from users_matches_profiles table against user_id 18.

Then it should match the question ids and answer ids in the users_profiles table. This should return all users who have the same answers to the questions as user_id 18.

Now the third step should pull out the user_names and user_ids of the users that got filtered in the earlier join.

Now i'm just having a slight problem here. When the query checks for questions and answers in users_profiles table it pulls all users who have got atleast one matching question and answer to the user_id 18 rather then checking for all of them. I cant get it to match all the question_ids and answer_ids.

Can anyone help me with this please before i start to pull my hair out :(

Thanks!

[txbakers]

First, keep it simple. Write the join between two tables to get the dataset you want.

Then, write the join between the other two tables so you ge the results you want.

Then finally find the common thread between all three, and it should be fairly simple to write the join.

Instead of using just the word "join" you should probably use "inner join"