[SOLVED] "dictionary" table with SQLite / TSQLite3Dataset
lazarus / 16-Databases

----------------------------------------------------------------------
totya · 2015-05-11 18:23

Hi!

I thinking on this problem few weeks ago. I try to explain what is my
problem.

For example, I have a table, name is "data":

id type          subject
1  money      yxgvdfgas
2  money      asfasfsdfasdf
3  warning    adfasfwefw

As you can see, type fields often equally. Good idea (correct database
structure I think!) if I don't store these fields as strings. I create
new table, and I store only the type fields, name is "types":

id type_name
1  money
2  warning

Then the "data" table is:
id type_id  subject
1  1           yxgvdfgas
2  1           asfasfsdfasdf
3  2           adfasfwefw

My question, I can create and fill "types" table, I can create and
fill "data" table (with type_id), but how can I show (and use) this
table like as original? With SQL query?

So, "data" table again this:
id type_id  subject
1  1           yxgvdfgas
2  1           asfasfsdfasdf
3  2           adfasfwefw

...and I want to see the original table
id type          subject
1  money      yxgvdfgas
2  money      asfasfsdfasdf
3  warning    adfasfwefw

I use TSQLite3Dataset.
It's a very simple question I think, but I don't know what is the
solution.

Thanks for any help!

----------------------------------------------------------------------
cdbc · 2015-05-11 18:49

Hi
Answer is SQL   Joins and referential integrity 
There are people here who are much stronger in SQL than I am, so i'll
step aside for a moment 
Regards Benny

----------------------------------------------------------------------
LuizAmérico · 2015-05-11 19:26

Dataset.SQL := 'Select data.id, types.name as Type, subject from data
left join types on data.type_id = types.id';
Dataset.open;

----------------------------------------------------------------------
totya · 2015-05-11 21:07

Quote from: LuizAmérico on May 11, 2015, 09:26:05 pmDataset.SQL :=
'Select data.id, types.name as Type, subject from data left join types
on data.type_id = types.id';
Dataset.open;

Hi!

This is the exactly, what I wanted. Very thank you!

Well, then the solution is the INNER JOIN (between two tables), or the
LEFT OUTER JOIN (between two or more tables)
Now I need data from two additional tables, so the my code this:

Code: [Select]Select data.id, types.name as type, types2.name as
type2, subject from data
left outer join types on data.type_id = types.id
left outer join types2 on data.type2_id = types2.id

edit:

... or simpler code with USING (type_id and type2_id fields needed):

Code: [Select]Select data.id, types.name as type, types2.name as
type2, subject from data
left outer join types USING (type_id)
left outer join types2 USING (type2_id);

Thanks again!