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

Question

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 |
+--------+---------+---------+

hey thank you everyone for your contributions, It looks like I have found the solution for me, but as a newbie here do I set the answer Iiked somewhere, I tried to vote on some responses but apparently not allowed!! Anyway, thankyou ALL!! — IEnumerable, Nov 27 '11 at 10:42

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

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

edited May 23 '17 at 11:50

Community

1
1

answered Nov 27 '11 at 10:21

Manse

37,765
10
83
108

+1,Perfect.BTW: I think it should be `IsNull(s.statusDesc,'Unkown')` – Mahmoud Gamal Nov 27 '11 at 10:27
@MGA - OK - rather than `null` ... i will update the answer ... thanks – Manse Nov 27 '11 at 10:29
I like the the second solution provided but its not executing. Im running it through php mysql_query() so I prob have a SPACE or comma in the wrong place! – IEnumerable Nov 27 '11 at 10:40
@IEnumerable - yep my fault - comma on the end of the select ... updated answer – Manse Nov 27 '11 at 10:48

score 1 · Answer 2 · answered Nov 27 '11 at 10:22

1

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

answered Nov 27 '11 at 10:22

pramodtech

6,300
18
72
111

score 0 · Answer 3 · answered Nov 27 '11 at 10:29

0

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

answered Nov 27 '11 at 10:29

Elias Hossain

4,410
1
19
33

1

thanksyou this one worked best for me, I looked up left outer join but the resuts are multiple records that does not suit me. Thankyou – IEnumerable Nov 27 '11 at 10:33

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

3 Answers3