SQLiteStudio : Problems With Views

Question

I'm trying to build a database for personal use, and part of the db will store the data of addresses, and the way I have done it is this:

Table 1: Address (Line 1 & 2) -- foreign key ----> Postal Codes

Table 2: Postal Codes -- foreign key ----> Locality (ex: cities)

Table 3: Locality -- foreign key ----> Provenience

Table 4: Provenience -- foreign key ----> States

Table 5: State -- foreign key ----> Countries

Table 6: Countries

PS: Tables from 1 --> 6 have nested foreign keys

I would like to know how can I make a view resulting all the data in Table 1 with all the corresponding data in Tables 2-->6, without repeating common columns ???

Answer 1

Thank you every one I have just managed to solve it, and here is how I have done it, showing only the fields I want, with the rows I want with out duplication. (Pardon me, I´m a noob)

Start with the fields of the first table you want it on your left,

SELECT [Table 1].Address1 AS [Address Line 1],
       [Table 2].Postal_Code AS [Postal Code]

  FROM [Table 1]

       INNER JOIN
       [Table 2] on [Table 1].Primary_key_Column = [Table 2].Foreign_key_Column

To make it simple for SQLite noods such as myself:

1. Choose only the columns you want from every table.
2. Order the chosen columns from left to right in the same order of your data base order hierarchy.
3. Write the SQLite view query making the order of appearance of your table's field go from top to bottom such as in the example I have written before.
4. In the "FROM" clause use only the first table.

So what if you want more fields from more tables (included tables with nested fields ?

Just do as follows

SELECT [Table 1].Address1    AS [Address Line 1],
       [Table 2].Postal_Code AS [Postal Code],
       [Table 3].Locality    AS [Locality],
       [Table 4].Provenience AS [Provenience]

  FROM [Table 1]      <---- You only need to mention the first table of the first field

       INNER JOIN
       [Table 2] on [Table 1].Primary_key_Column = [Table 2].Foreign_key_Column

       INNER JOIN
       [Table 3] on [Table 2].Primary_key_Column = [Table 3].Foreign_key_Column

       INNER JOIN
       [Table 4] on [Table 3].Primary_key_Column = [Table 4].Foreign_key_Column

I know that this solution could be seen as cumbersome, but its advantages are:

1. When ever you want to add a level you simple add a line at the end of "SELECT" bloc, and another one at the end of "INNER JOIN" bloc.
2. It will be easier for you to read and debug in case of problems in querying data.

That is the way it solved itself with me, but I'm ready to hear better answers