Is it possible selecting "optional" column in single query?

Question

I have three tables:

// table item
+-------+-------+----------+
|  ID   |  Cat  |  SubCat  |
+-------+-------+----------+
|  A001 |  001  |    001   |
+-------+-------+----------+
|  A002 |  001  |    000   |
+-------+-------+----------+

// table category
+-------+-------------+
|  ID   |  CatName    |
+-------+-------------+
|  001  |  MyCategory |
+-------+-------------+

// table subcategory
+-------+--------+---------------+
|  ID   |  CatID | SubCatName    |
+-------+------------------------+
|  001  |   001  | MySubCategory |
+-------+--------+---------------+

Cat means the category id the item belongs to

SubCat means the sub-category id the item belongs to

SubCat is optional, if no sub-category, 0 will be filled

I try to query:

select [Item].[ID], [Category].[CatName], [Subcategory].[SubCatName]
from [Item],
     [Category],
     [Subcategory]
where [Item].[Cat] = [Category].[ID]
  and [Item].[SubCat] = [Subcategory].[ID]

This will successfully select record A001, which is returns "A001", "MyCategory", "MySubCategory"

But I want to select record A002 too, since SubCat is optional field

e.g. "A002", "MyCategory", "N/A"

I tried to use CASE, but seems access didn't support it.

Is there any way to do this within single SQL query, for example using JOIN?

Answer 1

I think you want a LEFT JOIN:

select [Item].[ID], [Category].[CatName], [Subcategory].[SubCatName]
from ([Item] INNER JOIN
      [Category]
      on [Item].[Cat] = [Category].[ID]
     ) LEFT JOIN
     [Subcategory] 
     on [Item].[SubCat] = [Subcategory].[ID];

You should also learn to use explicit JOIN syntax, rather than commas in the FROM clause.