SQLite3 Relational bases

[Pe3s]

Hello, where can I find an example of a SQLite relational database written in lazarus?

[af0815]

Is something unclear with https://wiki.freepascal.org/SQLite , https://wiki.freepascal.org/TSqlite3_Master_Detail_Example and some more site ? found with searchin on google with lazarus wiki sqlite

and in the wiki is a tutorial https://wiki.freepascal.org/SQLdb_Tutorial1

[Pe3s]

Do I need to duplicate components when creating a relational database?

[JanRoza]

What components are you talking about?
Normally for databases you need one connection, one transaction and one or more queries per table in that database.

[Pe3s]

How do I build a SELECT query to display the entire contents of both tables?

[JanRoza]

Something like SELECT * FROM table2 WHERE field1 = table1.field1
Not sure what you want exactly but I think the SQL JOIN is what you are looking for.
I advise you to visit W3schools website https://www.w3schools.com/sql/, they have tutorials for almost anything you can do with SQL.

[Pe3s]

I want to combine these two queries

Code: Pascal  [Select][+][-]

1. SQLQuery1.SQL.Text := 'SELECT * FROM table';
2. SQLQuery2.SQL.Text := 'SELECT * FROM table_1 WHERE table_1.Col_id = :id';
3.  

[JanRoza]

Like I already mentioned, check out the SQL JOIN statement.

[balazsszekely]

@Pe3s

Like I already mentioned, check out the SQL JOIN statement.

Take a look at the union command, column count from table and table_1 must be the same. A better approach would be something like this:

Code: MySQL  [Select][+][-]

1.   SQLQuery1.SQL.Text := 'select tb.id, tb.firstname, tb.lastname, tb.salary from table tb' +
2.                         'union ' +
3.                         'select tb_1.id, tb_1.firstname, tb_1.lastname, tb_1.salary from table_1 tb_1 where tb_1.id = :id';        
4.  

[JanRoza]

Thanks GetMem, even better. 

[Pe3s]

Thank you 

[Pe3s]

I still have a question, how can we update the data in the table, e.g. after deleting the record, we are alive select or can we refresh the view differently?

[paweld]

Code: Pascal  [Select][+][-]

1. SQLQuery1.Refresh;

[Pe3s]

Why is SQLQuery1.Refresh; causes an error Operation cannot be performed on an inactive dataset.

[JanRoza]

I think you will have to show some code of how you delete and then refresh your table.
Now we can only guess.