CODEWITHSUNDEEP
sqloracle-databasesql-function
Himanshu Kawale
Himanshu Kawale

Reputation: 364

SQL | How to display "NA" if row doesn't exists, without switch case

I have two tables,

Table 1:

studentId department
S01 Mech
S02 Mech
S03 CSE

Table 2:

studentId Result
S01 Pass
S03 Fail

I want to display : studentId, Result of Mech department, for those students whose result is not given in table 2, result should show "Absent".

Desired output,

studentId Result
S01 Pass
S02 Absent

How can I do it without using switch cases? (Using only: Joins, Sub queries, Function, Group by, Having, Where, etc.) (i.e Basics only)

Using, SQL (Oracle)

I tried too much, but was unable to display "Absent" for "S02"

Upvotes: 0

Views: 67

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

Use a left join between the two tables, along with COALESCE to render Absent as the result for any mechanical student missing in the second table.

SELECT t1.studentId, COALESCE(t2.Result, 'Absent') AS Result
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t2.studentId = t1.studentId
WHERE t1.department = 'Mech';

Upvotes: 1

Related Questions