Recent

Author Topic: [SOLVED] "dictionary" table with SQLite / TSQLite3Dataset  (Read 2194 times)

totya

  • Hero Member
  • *****
  • Posts: 636
[SOLVED] "dictionary" table with SQLite / TSQLite3Dataset
« on: May 11, 2015, 08:23:34 pm »
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!
« Last Edit: May 11, 2015, 11:08:11 pm by totya »

cdbc

  • Sr. Member
  • ****
  • Posts: 257
    • http://www.cdbc.dk
Re: "dictionary" table with TSQLite3Dataset
« Reply #1 on: May 11, 2015, 08:49:05 pm »
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  :D
Regards Benny
If it ain't broke, don't fix it ;)

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
Re: "dictionary" table with TSQLite3Dataset
« Reply #2 on: May 11, 2015, 09:26:05 pm »
Dataset.SQL := 'Select data.id, types.name as Type, subject from data left join types on data.type_id = types.id';
Dataset.open;

totya

  • Hero Member
  • *****
  • Posts: 636
Re: "dictionary" table with TSQLite3Dataset
« Reply #3 on: May 11, 2015, 11:07:17 pm »
Dataset.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!
« Last Edit: May 11, 2015, 11:49:38 pm by totya »

 

TinyPortal © 2005-2018