CODEWITHSUNDEEP
phpsql
IEnumerable
IEnumerable

Reputation: 3790

SQL Query - Need to get a value not in a table

I have the following SQL for my PHP project, I need to receive the Module Status Text to match the status Id in the Module Table, but if the status id value doesn't exist, can I set a value in the SQL? This is a simple Query but Im looking for an efficient solution so I dont have to run 2 seperate queries.

Thanks in advance..

Below is what I have now but it does select the values that are not reference in ModuleStatus.

SELECT m.moduleID, m.moduleDesc,s.statusDesc,
FROM Modules m, ModuleStatus s 
WHERE s.statusID = m.statusID

Below is a simplified version of the results Im getting...
Module

+--------+---------+---------+
|   ID   |   Desc  |  Stat   |
+--------+---------+---------+
|   5    |   Car   |  1      |
+--------+---------+---------+
|   6    |   Bike  |  2      |
+--------+---------+---------+

ModuleStatus

+--------+---------+
|   ID   |   Desc  |
+--------+---------+
|   1    | on      |
+--------+---------+
|   0    | off     |
+--------+---------+

The results would be
Result

+--------+---------+---------+
|   ID   |   Desc  |  Stat   |
+--------+---------+---------+
|   5    |   Car   |  on     |
+--------+---------+---------+

But What I want is
Expect!!

+--------+---------+---------+
|   ID   |   Desc  |  Stat   |
+--------+---------+---------+
|   5    |   Car   |  on     |
+--------+---------+---------+
|   6    |   Bike  | Unknown |
+--------+---------+---------+

Upvotes: 1

Views: 161

Answers (3)

Manse
Manse

Reputation: 38147

You need a left outer join :

SELECT m.moduleID, m.moduleDesc,s.statusDesc,
FROM Modules m
LEFT OUTER JOIN ModuleStatus s 
ON s.statusID = m.statusID

Good examples in this answer -> What is the difference between "INNER JOIN" and "OUTER JOIN"?

As suggested by @MGA ... you could change the missing values to "unknown" by updating the select statement :

SELECT m.moduleID, m.moduleDesc, IsNull(s.statusDesc,'Unknown')
FROM Modules m
LEFT OUTER JOIN ModuleStatus s 
ON s.statusID = m.statusID

Upvotes: 1

Elias Hossain
Elias Hossain

Reputation: 4469

SELECT m.moduleID AS ID, m.moduleDesc AS Desc, 
       ISNULL(s.statusDesc, 'Unknown') AS Stat
FROM Modules m
LEFT OUTER JOIN ModuleStatus s 
ON s.statusID = m.statusID

Upvotes: 0

pramodtech
pramodtech

Reputation: 6270

SELECT m.moduleID, m.moduleDesc,s.statusDesc,
FROM Modules m left outer join ModuleStatus s 
on s.statusID = m.statusID

Upvotes: 1

Related Questions