• From: Ukraine
  • Registered: 2020-06-11
  • Posts: 62

Topic: Report with data from two subordinate tables.

Hello everybody!
There are three tables: one basic and two subordinates associated by ID. When trying to create a report, I get not correct data.
Tell me, what's wrong with my script?
Thanks in advance!

/Translation from Google/

Всем здравствуйте!
Есть три таблицы: одна основная и две подчиненные, связаные по id. При попытке создания отчета, получаю не корректные данные.
Подскажите, что не так с моим скриптом?
Заранее спасибо!

/Перевод с Гугл/

Post's attachments

Attachment icon SQL Report.rar 295.2 kb, 389 downloads since 2021-12-31 

I'm just learning
  • From: Ukraine
  • Registered: 2020-06-11
  • Posts: 62

Re: Report with data from two subordinate tables.

Slightly changed the request:

SELECT * FROM 
(SELECT ADR.id as ID, strftime("%d.%m.%Y %H:%M",ADR.data) as Data,ADR.adres as MyAdres,(FIRST_TAB.column_1||' - '||FIRST_TAB.column_2) as Tab1,'' as Tab2 FROM ADR
INNER JOIN              
FIRST_TAB ON ADR.id=FIRST_TAB.id_ADR and ADR.id={TableGrid1}
UNION ALL
SELECT ADR.id as ID, strftime("%d.%m.%Y %H:%M",ADR.data) as Data,ADR.adres as MyAdres,'' as Tab1,(SECOND_TAB.column_1||' - '||SECOND_TAB.column_2) as Tab2 FROM ADR
INNER JOIN
SECOND_TAB ON ADR.id=SECOND_TAB.id_ADR and ADR.id={TableGrid1})
ORDER BY ID

The resulting records are correct, but I get empty lines. This spoils the entire type of report. I ask for help!
/Translation from Google/


Немного изменил запрос:

SELECT * FROM 
(SELECT ADR.id as ID, strftime("%d.%m.%Y %H:%M",ADR.data) as Data,ADR.adres as MyAdres,(FIRST_TAB.column_1||' - '||FIRST_TAB.column_2) as Tab1,'' as Tab2 FROM ADR
INNER JOIN              
FIRST_TAB ON ADR.id=FIRST_TAB.id_ADR and ADR.id={TableGrid1}
UNION ALL
SELECT ADR.id as ID, strftime("%d.%m.%Y %H:%M",ADR.data) as Data,ADR.adres as MyAdres,'' as Tab1,(SECOND_TAB.column_1||' - '||SECOND_TAB.column_2) as Tab2 FROM ADR
INNER JOIN
SECOND_TAB ON ADR.id=SECOND_TAB.id_ADR and ADR.id={TableGrid1})
ORDER BY ID

Результирующие записи верны, но получаю пустые строки. Это портит весь вид отчета. Прошу помощи!
/Перевод с Гугл/

I'm just learning
  • From: Ukraine
  • Registered: 2020-06-11
  • Posts: 62

Re: Report with data from two subordinate tables.

Собственно, решение найдено:

SELECT ADR.id as ID,strftime("%d.%m.%Y %H:%M",ADR.data) as Data,ADR.adres as MyAdres,
      (SELECT group_concat(FIRST_TAB.column_1||' ('||FIRST_TAB.column_2||')') FROM FIRST_TAB WHERE FIRST_TAB.id_ADR=ADR.id) as Tab1,
      (SELECT group_concat(SECOND_TAB.column_1||' ('||SECOND_TAB.column_2||')') FROM SECOND_TAB WHERE SECOND_TAB.id_ADR=ADR.id) as Tab2
FROM ADR WHERE ADR.id={TableGrid1}

Всем спасибо и с наступающим Новим годом!

Actually, the solution is found:

SELECT ADR.id as ID,strftime("%d.%m.%Y %H:%M",ADR.data) as Data,ADR.adres as MyAdres,
      (SELECT group_concat(FIRST_TAB.column_1||' ('||FIRST_TAB.column_2||')') FROM FIRST_TAB WHERE FIRST_TAB.id_ADR=ADR.id) as Tab1,
      (SELECT group_concat(SECOND_TAB.column_1||' ('||SECOND_TAB.column_2||')') FROM SECOND_TAB WHERE SECOND_TAB.id_ADR=ADR.id) as Tab2
FROM ADR WHERE ADR.id={TableGrid1}

Thank you all and Happy New Year!

I'm just learning