CODEWITHSUNDEEP
mysql
Ashok Gujjar
Ashok Gujjar

Reputation: 441

How to make condition to be optional in MySQL, so that if my condition becomes wrong, query should be execute

I have to MySQL tables named "QUIZ" and "CHAPTER" as shown below

QUIZ

qid | quizname | chapterid
=========================
1   | quiz1    |     0
-------------------------
2   | quiz3    |     1
-------------------------
3   | quiz3    |     2
-------------------------

CHAPTER:

chapterid | chaptername  
=========================
1         | chpter1    
-------------------------
2         | chpter2    
-------------------------
3         | chpter3    
-------------------------

Now i want to get details of quiz from QUIZ table, where quizid=1. My sql query is 

"SELECT quiz.qid, quiz.quizname, chapter.chaptername from quiz, chapter
 where quiz.chapterid=chapter.chapterid and quiz.qid=1"

but i get no result. So please explain me that what can be a problem?

Upvotes: 2

Views: 50

Answers (2)

eLemEnt
eLemEnt

Reputation: 1801

since the chapterId associate with qid = 1 is 0 which is not matching with any other row from another table so your query will return empty records so you better change AND to OR and try to run

"SELECT quiz.qid, quiz.quizname, chapter.chaptername from quiz, chapter
 where quiz.chapterid=chapter.chapterid OR quiz.qid=1"

with join

select quiz.qid,quiz.quizname,chapter.chaptername from quiz left outer join chapter On quiz.chapterid = chapter.chapterid

Upvotes: 0

Whirl Mind
Whirl Mind

Reputation: 884

You can see that chapterid 0 is not present in the Chapter table and that is why the query does not return anything (rightfully so). If you do want it to return the quizname from quiz table, even if chapter id is not valid, then you should use a left outer join. Like this : 

SELECT quiz.qid, quiz.quizname, chapter.chaptername 
from quiz 
left outer join chapter On quiz.chapterid=chapter.chapterid 
Where quiz.qid=1

Obviously, the chaptername returned will be null. (Note : I put the code above to show you the left outer join idea. Check out any MySql-specific syntax I might have overlooked).

Upvotes: 2

Related Questions