I have two tables, table surveys and table survey_votes.
I want to select all surveys from table surveys
select * from surveys s
where there is no entry in table survey_votes equal to a certain user_id that references the survey from table surveys.
Example:
Table surveys
| id | question |
|---|---|
| 1 | What food do you like best? |
| 2 | What is your favorite car brand? |
Table survey_votes
| id | user_id | survey_id |
|---|---|---|
| 1 | 100 | 1 |
| 1 | 101 | 1 |
| 1 | 101 | 2 |
- If user_id = 100, only survey 2 should be selected. If user_id = 101, no survey should be selected.
- If user_id = 102, both surveys should be selected.
The output table should consist of all columns of the original table surveys. Therefore, for user 102 it should look as follows:
| id | question |
|---|---|
| 1 | What food do you like best? |
| 2 | What is your favorite car brand? |
Any help is much appreciated!
CodePudding user response：
SELECT * FROM `survey` s WHERE s.id NOT IN
(SELECT sv.`survey_id` FROM `survey_votes` sv WHERE sv.`user_id`=102)
This should help, change "102" with your input user_id