MySQL SELECT query where to find records that are not in the related table

Question

I am quiet new to programming so i need some help for the query in the below scenario.

user table

uid (PK)| name  | score
------------------
   1    | john  | 20
   2    | mary  | 40
   3    | david | 60
   4    | nancy | 80

question_tb table

qid|question
-------------
1  | a     
2  | b     
3  | c     
4  | d

question_user table

quid | user_id (FK) | question_id (FK)
--------------------------------
1    | 1            | 1
2    | 2            | 1
3    | 1            | 2
4    | 3            | 3

As above shows the table structure of the database. question_user table holds the questions that have been answered by a particular user. I want to get the list of questions in DESC manner that particular user has not been answered.

What did you try? How did it fail to meet your expectations? — symcbean, Jul 11 '18 at 12:11
Possible duplicate of [SELECT \* WHERE NOT EXISTS](https://stackoverflow.com/questions/915643/select-where-not-exists) or [mysql-select-where-not-in-table](https://stackoverflow.com/questions/354002/mysql-select-where-not-in-table) — Raymond Nijland, Jul 11 '18 at 12:15
Possible duplicate of [SQL - find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) — Feras Al Sous, Jul 11 '18 at 13:49

score 1 · Accepted Answer · answered Jul 11 '18 at 12:21

1

The following query should give you questions that user hasn't answered.

SELECT *
FROM question_tb as q
LEFT JOIN question_user as qu on q.qid = qu.question_id AND qu.user_id = USER_ID_HERE
WHERE  qu.user_id IS NULL;

answered Jul 11 '18 at 12:21

Lyubetrix

51
3

score 0 · Answer 2 · edited Jul 11 '18 at 13:17

0

If you want to get the Questions not answered by particular user in DESC then use below query

SELECT * FROM questions WHERE qid NOT IN (SELECT question_id FROM `question_user` WHERE uid = USER-ID) ORDER BY qid DESC;

edited Jul 11 '18 at 13:17

Siddharth Jain

336
2
9

answered Jul 11 '18 at 12:28

D P

328
1
8

score 0 · Answer 3 · answered Jul 11 '18 at 13:42

Try this:

Sample data:

create table users (uid int, name varchar(10), score int);
insert into users values
(1, 'john', 20),
(2, 'mary' , 40),
(3, 'david', 60),
(4, 'nancy', 80);
create table question_tb (qid int, question char(1));
insert into question_tb values
(1, 'a'),     
(2, 'b'),    
(3, 'c'),     
(4, 'd');   
create table question_user (quid int, user_id int, question_id int);
insert into question_user values
(1, 1, 1),
(2, 2, 1),
(3, 1, 2),
(4, 3, 3);

T-SQL:

select uid, qid from users u
cross join question_tb q
where not exists(
    select 1 from question_user
    where u.uid = user_id and q.qid = question_id)
order by uid, qid

MySQL SELECT query where to find records that are not in the related table

3 Answers3