Creating a database view with boolean logic

Question

My google search skills have failed me, and I am not a database expert by any means! I have a very simple database schema that looks like this:

properties_id in the CANDY table is a foreign key to id in the EXPENSIVE_PROPERTIES table. The properties_id is only set if the candy is expensive. If it is expensive, then the corresponding values in the EXPENSIVE_PROPERTIES table will get filled in.

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

• request all of the properties in the CANDY table, like name and color
• optionally get the expensive properties if the properties_id value is not null

In the past, I have done two queries, but this is silly since I should be able to create a view that aggregates all of the properties into one table, and just leaves the other fields blank if there isn't a corresponding id in the EXPENSIVE_PROPERTIES table.

Can anyone tell me how to create this view in SQLite? I assume it's possible, but couldn't figure it out from the docs:

The IF NOT EXISTS elements don't seem to have anything to do with the existence of a particular field value.

UPDATE

The question has been answered, but one thing I find weird is that in some cases I see duplicate entries. I'm using SQLite Administrator, and when I execute the CREATE VIEW statement and look in the results tab, I get unique entries. If I just do a SELECT statement, I get duplicate rows. If I create the view, and then do a SELECT * FROM myview; I also see duplicate rows. Can anyone shed some light on this? Should I create a new question?

UPDATE #2

Never mind, I found my mistake -- I had two tables after FROM, which causes the duplication.

Answer 1

You can use LEFT OUTER JOIN to retrieve data from another table that may not have data for some rows in your primary table.

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

The IF NOT EXISTS does not refer to dealing with data that does not exist. Rather, it means it will create the view if it does not exist already. This is to prevent errors when running the statement more than once.

Answer 2

I'm not familiar with SQLite, but otherwise there will be a need for LEFT OUTER JOIN:

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

This will select anything matching and will leave NULLs for ep.* that don't exist for the CANDY table.

Answer 3

You want to perform a left join in your view. This will bring back everything from the first table in the join and everything from the second table that matches. If nothing matches (the candy is not expensive), the fields from the second table will be null.

select * from candy left outer join expensive_properties on candy.id = expensive_properties.id