CODEWITHSUNDEEP
mysqlsqljoin
Tom
Tom

Reputation: 173

MySQL combining datas from 2 tables

TABLE surveys:

id   survey_title       date           active
1    Example Survey     1508842162     1
2    Example Survey2    1508842162     1


TABLE survey_user_relation:

id    user_id       survey_id(foregin_key)       active
1     1             1                            1
2     1             2                            0
3     2             1                            1

I have two table as above. column active represents if a survey is closed from all the users in survey table and closed from just one user in survey_user_relation table. user_id and survey_id are set to UNIQUE key together so that one user can be related to the same survey only once. I am trying to list all the surveys and I want to know if a user is related with that survey in one query if possible.

I tried

SELECT * 
  FROM surveys s
  LEFT 
  JOIN survey_user_relation u
    ON u.survey_id = s.id 
 WHERE u.user_id = 2

But it only gives one survey I need to see all the surveys and know if the user is related.

Thanks in advance

Edit:

I need to see something like

id   survey_title       date           active     user_id
1    Example Survey     1508842162     1          2
2    Example Survey2    1508842162     1          NULL

Upvotes: 0

Views: 39

Answers (2)

TobiasHH
TobiasHH

Reputation: 125

This should do the trick. 

SELECT s.*, u.* 
    FROM surveys s
    LEFT JOIN survey_user_relation u
    ON u.survey_id = s.id AND u.user_id = 2;

With where he is only getting entries which really have user_id = 2. On a left join he checks if one is there otherwise it returns null for the columns. By the way you dont need the row ID on your second table. Just set survey_id and user_id to primary.

Upvotes: 1

Prabhat G
Prabhat G

Reputation: 3029

I think you're interested in this result:

SEELCT distinct s.survey_title, u.user_id
FROM surveys AS S LEFT JOIN survey_user_relation AS U 
ON U.survey_id = S.id

Upvotes: 0

Related Questions