My search abilities have unsuccessful me, and i'm not really a database expert at all! I've got a quite simple database schema that appears such as this:

database schema

qualities_id within the Chocolate table is really a foreign answer to id within the Costly_Qualities table. The qualities_id is just set if the chocolate is costly. If it's costly, then your corresponding values within the Costly_Qualities table can get completed.

When I wish to get a listing from the candies, I essentially do:

  • request all the qualities within the Chocolate table, like title and color
  • optionally obtain the costly qualities when the qualities_id value isn't null

Previously, I've done two queries, but this really is silly since i have should have the ability to produce a view that aggregates all the qualities into one table, and merely leaves another fields blank if there is not a corresponding id within the Costly_Qualities table.

Can anybody let me know how you can create this view in SQLite? I suppose it is possible, but could not decipher it from the docs:

alt text

The Otherwise Is available elements don't appear to possess anything related to the presence of a specific area value.


The question continues to be clarified, only one factor I've found strange is the fact that in some instances I see duplicate records. I am using SQLite Administrator, so when I execute the CREATE VIEW statement and appear within the results tab, I recieve unique records. Basically just perform a Choose statement, I recieve duplicate rows. Basically produce the view, after which perform a Choose * FROM myview I additionally see duplicate rows. Can anybody shed some light about this? Must I produce a new question?


Let alone, I discovered my mistake -- I'd two tables after FROM, which in turn causes the duplication.

You should use [cde] to retrieve data from another table that won't have data for many rows inside your primary table.


The create view vCandy as select,, c.color,, ep.chocolate, ep.gold_foil from Candy c left outer join Expensive_Properties ep on c.properties_id = doesn't make reference to coping with data that doesn't exist. Rather, this means it'll produce the view if it doesn't exist already. This really is to avoid errors when running the statement more often than once.

You need to execute a left participate in your view. This can restore from the very first table within the join and from the 2nd table that suits. If nothing matches (the chocolate isn't costly), the fields in the second table is going to be null.

choose * from chocolate left outer join costly_qualities on =

I am unfamiliar with SQLite, but otherwise you will see an excuse for LEFT OUTER JOIN:


This can choose anything matching and can leave NULLs for air.* that do not exists for the Chocolate table.