Creating a Logical Database View

Question

My Google search skills let me down and I'm not a database expert! I have a very simple database schema that looks like this:

database schema http://bit.ly/adeUVL

properties_id in the CANDY table is the foreign key to id in the EXPENSIVE_PROPERTIES table. The properties_id property is set only if the candies are expensive. If it is expensive, then the corresponding values ​​in the EXPENSIVE_PROPERTIES table will be populated.

When I want to get a list of chocolates, I basically want to do this:

• query all the properties in the CANDY table, for example name and color
• optionally get expensive properties if properties_id is not null

In the past, I performed two queries, but this is stupid, since I should be able to create a view that aggregates all the properties into one table and simply leaves the remaining fields empty if there are no corresponding id in the EXPENSIVE_PROPERTIES table.

Can someone tell me how to create this view in SQLite? I assume this is possible, but could not figure it out from the docs :

IF NOT EXISTS elements do not seem to have anything to do with the existence of a particular field value.

UPDATE

, , , - , . SQLite Administrator, CREATE VIEW , . SELECT, . , SELECT * FROM myview; . - ? ?

# 2

, - FROM, .

Answer 1

LEFT OUTER JOIN , .

create view vCandy as
select c.id, c.name, c.color, c.properties,
 ep.chocolate, ep.gold_foil
from Candy c
left outer join Expensive_Properties ep on c.properties_id = ep.id

IF NOT EXISTS , . , , , . .

Answer 2

. , , . ( ), .

select * from candy left external join road_properties candy.id = _properties.id

Answer 3

SQLite, LEFT OUTER JOIN:

SELECT * FROM candy c
    LEFT OUTER JOIN expensive_properties ep ON c.id = ep.id
WHERE [your criteria]

NULL ep. *, CANDY.