CODEWITHSUNDEEP
mysql
GeekySelene
GeekySelene

Reputation: 927

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

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.

Upvotes: 0

Views: 617

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37357

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

Upvotes: 0

D P
D P

Reputation: 328

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;

Upvotes: 0

Lyubetrix
Lyubetrix

Reputation: 51

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;

Upvotes: 1

Related Questions