Geek Answers Handbook

SQLite Combining related data (tree) between two tables with several potential intermediate relationships

I have a SQLite database (version 3.8.1) with a somewhat unusual schema that cannot be modified.

For the purposes of these questions, there are 5 tables (from t1 to t5), and I need to create a summary report using the data from t1 and t5, but the data that I need to reference in t5 can be obtained only on the basis of the attitude to the records from t1 to t4 .

To help clarify, imagine that t1 contains data about a document. Subsequently, a document can go from 1 to 4 iterations (with different fields available at each iteration, so 5 different tables, and not just a flag in 1 table to indicate which iteration it is on).

I am wondering if the original record / document (contained in t1) reached the final iteration (there is no ParentGUID in t5, which when tracking the chain of tables ultimately reaches t1 or not).

t1 has a GUID field (text), and t2 - t5 has a GUID and ParentGUID field (also text). The ParentGUID field in t2-t5 does not need to be filled in (in some cases, iteration of the documentation may be skipped), but when ParentGUID is set, it will always be the GUID from the previous table (for example, if t5 has the ParentGuid value, it will be the GUID from t1, t2 , t3 OR t4).

This means that I want all the individual entries from t1, and then for each value (or values) from t5, if any, or null if not.

ParentGuid t5 GUID t4, ParentGuid t4 GUID t1, , t1 .

, ParentGUID > GUID-, t1 > t5, > :

t1 > t2 > t3 > t4 > t5
t1 > t2 > t3 > t5
t1 > t2 > t4 > t5
t1 > t2 > t5
t1 > t3 > t4 > t5
t1 > t3 > t5
t1 > t4 > t5
t1 > t5

:

Possible relationship paths from T1 to T5

:

CREATE TABLE Table1
    ("GUID" TEXT, "Name" TEXT)
;

CREATE TABLE Table2
    ("GUID" TEXT, "ParentGUID" TEXT)
;

CREATE TABLE Table3
    ("GUID" TEXT, "ParentGUID" TEXT)
;

CREATE TABLE Table4
    ("GUID" TEXT, "ParentGUID" TEXT)
;

CREATE TABLE Table5
    ("GUID" TEXT, "Name" TEXT, "Amount" REAL, "ParentGUID" TEXT)
;

INSERT INTO Table1
    ("GUID", "Name")
VALUES
    ('ABC', 'A1')
;


INSERT INTO Table1
    ("GUID", "Name")
VALUES
    ('DEF', 'A2')
;

INSERT INTO Table1
    ("GUID", "Name")
VALUES
    ('GHI', 'A3')
;

INSERT INTO Table2
    ("GUID", "ParentGUID")
VALUES
    ('JKL', 'GHI')
;

INSERT INTO Table2
    ("GUID", "ParentGUID")
VALUES
    ('MNO', '')
;

INSERT INTO Table2
    ("GUID", "ParentGUID")
VALUES
    ('PQR', 'GHI')
;

INSERT INTO Table3
    ("GUID", "ParentGUID")
VALUES
    ('STU', 'MNO')
;

INSERT INTO Table3
    ("GUID",  "ParentGUID")
VALUES
    ('STU', 'GHI')
;

INSERT INTO Table3
    ("GUID", "ParentGUID")
VALUES
    ('VWX', 'PQR')
;


INSERT INTO Table4
    ("GUID", "ParentGUID")
VALUES
    ('YZA', 'VWX')
;

INSERT INTO Table4
    ("GUID", "ParentGUID")
VALUES
    ('BCD', '')
;

INSERT INTO Table4
    ("GUID", "ParentGUID")
VALUES
    ('EFG', 'GHI')
;

INSERT INTO Table5
    ("GUID", "ParentGUID", "Amount", "Name" )
VALUES
    ('HIJ', 'EFG', -500, 'E3')
;


INSERT INTO Table5
    ("GUID", "ParentGUID", "Amount", "Name" )
VALUES
    ('KLM', 'YZA', -702, 'E2')
;


INSERT INTO Table5
    ("GUID", "ParentGUID", "Amount", "Name" )
VALUES
    ('NOP', '', 220, 'E8')
;

INSERT INTO Table5
    ("GUID", "ParentGUID", "Amount", "Name" )
VALUES
    ('QRS', 'GHI', 601, 'E4')
;

, , t1, t5 ( , ), group_concat Name t5.

, :

t1.Name   total(t5.Amount)   group_concat(t5.Name)
--------------------------------------------------
A1                   0.00  
A2                   0.00  
A3                -601.00    E2,E3,E4

, ... Total/Group_Concat ( - , , "E4, E4, E4, E4, E2, E3, E3, E4, E4..." ), t1 t5 (601.00, E4).

, E4/601.00 t1 GHI:

SELECT DISTINCT t1.guid "OriginalGuid", t1.name "OriginalName", TOTAL(t5."Amount") as "TotalAmount", group_concat(t5.Name) AS "FinalNames"
FROM 
Table1 t1
LEFT  JOIN Table5 t5 ON (t1.GUID=t5.ParentGUID)
LEFT  JOIN Table4 t4 ON (t1.GUID=t4.ParentGuid AND t5.ParentGuid=t4.Guid)
LEFT  JOIN Table3 t3 ON (t1.GUID=t3.ParentGuid AND (t4.ParentGuid=t3.Guid OR t5.ParentGuid=t3.Guid))
LEFT  JOIN Table2 t2 ON (t1.GUID=t2.ParentGuid AND (t3.ParentGuid=t2.Guid AND ((t4.ParentGuid=t3.Guid And t5.ParentGuid=t4.guid) or (t5.ParentGuid=t3.Guid)) OR (t4.ParentGuid=t2.Guid and t5.ParentGuid=t4.Guid) OR (t5.ParentGuid=t2.Guid)))
GROUP BY t1.GUID;

, , -, ( -, , , , , - SQL , ).

, , , /, !

SQL Fiddle: http://sqlfiddle.com/#!5/1a2ac/55

.

+4
3

cha , , 2 5.

CREATE TABLE TableRel
    ("GUID" TEXT, "ParentGUID" TEXT, "TB" TEXT);

insert into TableRel
select GUID, ParentGUID, 'TABLE2'
FROM TABLE2
UNION ALL
select GUID, ParentGUID, 'TABLE3'
FROM TABLE3
UNION ALL
select GUID, ParentGUID, 'TABLE4'
FROM TABLE4
UNION ALL
select GUID, ParentGUID, 'TABLE5'
FROM TABLE5
;

UPDATE

1.

WITH RECURSIVE Table1Descendants(GUID, DescendantGUID,generation) as (
  select t1.GUID, Rel.GUID ,1
  from Table1 t1
  inner join TableRel rel
  on t1.GUID= Rel.ParentGUID
  UNION ALL
  select td.GUID, Rel.GUID, td.generation+1
  from TableRel Rel
  inner join Table1Descendants td
  on td.DescendantGUID= Rel.ParentGUID
  ) 
select t1.guid , t1.name , coalesce(sum(t5.Amount) ,0)
from Table1 as t1
left join Table1Descendants
on t1.GUID = Table1Descendants.GUID
left join Table5 as t5
on t5.GUID = Table1Descendants.DescendantGUID
group by t1.guid,t1.name
order by t1.name;

5.

WITH RECURSIVE Table1Ancestors(GUID, AncestorGUID) as (
  select t5.GUID, Rel.ParentGUID 
  from Table5 t5
  inner join TableRel rel
  on t5.GUID= Rel.GUID
  UNION ALL
  select ta.GUID, Rel.ParentGUID
  from TableRel Rel
  inner join Table1Ancestors ta
  on ta.AncestorGUID= Rel.GUID
  ) 
select t1.guid , t1.name , coalesce(sum(t5.Amount) ,0)
from Table1 as t1
left join Table1Ancestors
on t1.GUID = Table1Ancestors.AncestorGUID
left join Table5 as t5
on t5.GUID = Table1Ancestors.GUID
group by t1.guid,t1.name
order by t1.name;

, 3.8.3 SQLite CTE, SQLite, SQLFidle, PostgreSQL, , PostgreSQL total group_concat.

(SqlFiddle), SQLite 3.8.3 :

select t1.guid "OriginalGuid", t1.name "OriginalName", TOTAL(t5."Amount") as "TotalAmount", group_concat(t5.Name) AS "FinalNames"
from Table1 as t1
left join
(
  select t1.GUID, Rel.GUID as DescendantGUID, 1
  from Table1 t1
  inner join TableRel rel
  on t1.GUID= Rel.ParentGUID
  UNION ALL
  select t1.GUID, Rel2.GUID, 2
  from Table1 t1
  inner join TableRel rel1
  on t1.GUID= Rel1.ParentGUID
  inner join TableRel rel2
  on Rel1.GUID= Rel2.ParentGUID
  UNION ALL
  select t1.GUID, Rel3.GUID, 3
  from Table1 t1
  inner join TableRel rel1
  on t1.GUID= Rel1.ParentGUID
  inner join TableRel rel2
  on Rel1.GUID= Rel2.ParentGUID
  inner join TableRel rel3
  on Rel2.GUID= Rel3.ParentGUID
  UNION ALL
  select t1.GUID, Rel4.GUID, 4
  from Table1 t1
  inner join TableRel rel1
  on t1.GUID= Rel1.ParentGUID
  inner join TableRel rel2
  on Rel1.GUID= Rel2.ParentGUID
  inner join TableRel rel3
  on Rel2.GUID= Rel3.ParentGUID
  inner join TableRel rel4
  on Rel3.GUID= Rel4.ParentGUID
  ) as Table1Descendants
on t1.GUID = Table1Descendants.GUID
left join Table5 as t5
on t5.GUID = Table1Descendants.DescendantGUID
group by t1.guid,t1.name

:

OriginalGuid    OriginalName    TotalAmount FinalNames
ABC             A1              0.0 
DEF             A2              0.0 
GHI             A3              -601.0      E3,E2,E4
+2

. , (, ), T1 group_concat :

SQL Fiddle

SQLite (SQL.js):

CREATE TABLE Table1
    ("GUID" TEXT, "Name" TEXT)
;

CREATE TABLE Table2
    ("GUID" TEXT, "ParentGUID" TEXT)
;

CREATE TABLE Table3
    ("GUID" TEXT, "ParentGUID" TEXT)
;

CREATE TABLE Table4
    ("GUID" TEXT, "ParentGUID" TEXT)
;

CREATE TABLE Table5
    ("GUID" TEXT, "Name" TEXT, "Amount" REAL, "ParentGUID" TEXT)
;

INSERT INTO Table1
    ("GUID", "Name")
VALUES
    ('ABC', 'A1')
;


INSERT INTO Table1
    ("GUID", "Name")
VALUES
    ('DEF', 'A2')
;

INSERT INTO Table1
    ("GUID", "Name")
VALUES
    ('GHI', 'A3')
;

INSERT INTO Table2
    ("GUID", "ParentGUID")
VALUES
    ('JKL', 'GHI')
;

INSERT INTO Table2
    ("GUID", "ParentGUID")
VALUES
    ('MNO', '')
;

INSERT INTO Table2
    ("GUID", "ParentGUID")
VALUES
    ('PQR', 'GHI')
;

INSERT INTO Table3
    ("GUID", "ParentGUID")
VALUES
    ('STU', 'MNO')
;

INSERT INTO Table3
    ("GUID",  "ParentGUID")
VALUES
    ('STU', 'GHI')
;

INSERT INTO Table3
    ("GUID", "ParentGUID")
VALUES
    ('VWX', 'PQR')
;


INSERT INTO Table4
    ("GUID", "ParentGUID")
VALUES
    ('YZA', 'VWX')
;

INSERT INTO Table4
    ("GUID", "ParentGUID")
VALUES
    ('BCD', '')
;

INSERT INTO Table4
    ("GUID", "ParentGUID")
VALUES
    ('EFG', 'GHI')
;

INSERT INTO Table5
    ("GUID", "ParentGUID", "Amount", "Name" )
VALUES
    ('HIJ', 'EFG', -500, 'E3')
;


INSERT INTO Table5
    ("GUID", "ParentGUID", "Amount", "Name" )
VALUES
    ('KLM', 'YZA', -702, 'E2')
;


INSERT INTO Table5
    ("GUID", "ParentGUID", "Amount", "Name" )
VALUES
    ('NOP', '', 220, 'E8')
;

INSERT INTO Table5
    ("GUID", "ParentGUID", "Amount", "Name" )
VALUES
    ('QRS', 'GHI', 601, 'E4')
;

1:

SELECT t1.GUID, group_concat(o.Name), COALESCE(SUM(o.Amount), 0.0) TotalAmount
FROM Table1 t1 LEFT JOIN
(
SELECT t1.GUID, t5.Name, t5.Amount
FROM 
Table1 t1
INNER JOIN Table5 t5 ON (t1.GUID=t5.ParentGUID)
UNION ALL
SELECT t1.GUID, t5.Name, t5.Amount
FROM 
Table1 t1
INNER JOIN Table4 t4 ON (t1.GUID=t4.ParentGuid)
INNER JOIN Table5 t5 ON (t5.ParentGuid=t4.Guid)
UNION ALL
SELECT t1.GUID, t5.Name, t5.Amount
FROM 
Table1 t1
INNER JOIN Table3 t3 ON (t1.GUID=t3.ParentGuid)
INNER JOIN Table5 t5 ON (t5.ParentGuid=t3.Guid)
UNION ALL
SELECT t1.GUID, t5.Name, t5.Amount
FROM 
Table1 t1
INNER JOIN Table3 t3 ON (t1.GUID=t3.ParentGuid)
INNER JOIN Table4 t4 ON (t4.ParentGuid=t3.Guid)
INNER JOIN Table5 t5 ON (t5.ParentGuid=t4.Guid)
UNION ALL
SELECT t1.GUID, t5.Name, t5.Amount
FROM 
Table1 t1
INNER JOIN Table2 t2 ON (t1.GUID=t2.ParentGuid)
INNER JOIN Table5 t5 ON (t5.ParentGuid=t2.Guid)
UNION ALL
SELECT t1.GUID, t5.Name, t5.Amount
FROM 
Table1 t1
INNER JOIN Table2 t2 ON (t1.GUID=t2.ParentGuid)
INNER JOIN Table4 t4 ON (t4.ParentGuid=t2.Guid)
INNER JOIN Table5 t5 ON (t5.ParentGuid=t4.Guid)
UNION ALL
SELECT t1.GUID, t5.Name, t5.Amount
FROM 
Table1 t1
INNER JOIN Table2 t2 ON (t1.GUID=t2.ParentGuid)
INNER JOIN Table3 t3 ON (t3.ParentGuid=t2.Guid)
INNER JOIN Table5 t5 ON (t5.ParentGuid=t3.Guid)
UNION ALL
SELECT t1.GUID, t5.Name, t5.Amount
FROM 
Table1 t1
INNER JOIN Table2 t2 ON (t1.GUID=t2.ParentGuid)
INNER JOIN Table3 t3 ON (t3.ParentGuid=t2.Guid)
INNER JOIN Table4 t4 ON (t4.ParentGuid=t3.Guid)
INNER JOIN Table5 t5 ON (t5.ParentGuid=t4.Guid)
) o ON t1.GUID = o.GUID
GROUP BY t1.GUID

:

| GUID | group_concat(o.Name) | TotalAmount |
|------|----------------------|-------------|
|  ABC |                      |         0.0 |
|  DEF |                      |         0.0 |
|  GHI |             E2,E3,E4 |      -601.0 |
+1

, , DISTINCT , TOTAL CONCAT. , DISTINCT. t1 t5, .

SELECT sq1.guid "OriginalGuid", sq1.name "OriginalName", TOTAL(sq1."Amount") as "TotalAmount", group_concat(sq1.FinalNames) AS "FinalNames"
FROM
(SELECT DISTINCT t1.guid, t1.name, t5."Amount", t5.Name AS "FinalNames"
  FROM Table1 t1
  LEFT JOIN  Table2 t2 ON (t2.ParentGUID = t1.GUID)
  LEFT JOIN  Table3 t3 ON (t3.ParentGUID = t2.GUID
                           OR (t3.ParentGUID = t1.GUID))
  LEFT JOIN  Table4 t4 ON (t4.ParentGUID = t3.GUID 
                           OR (t4.ParentGUID = t2.GUID) 
                           OR (t4.ParentGUID = t1.GUID))
  LEFT JOIN  Table5 t5 ON (t5.ParentGUID = t4.GUID 
                           OR (t5.ParentGUID = t3.GUID) 
                           OR (t5.ParentGUID = t2.GUID) 
                           OR (t5.ParentGUID = t1.GUID))) sq1
GROUP BY sq1.guid;

. SQLite, SQL Server text - , 2 . , SQLite , , , VARCHAR, SQL- .

Without knowing your complete structure, I would suggest that each table has a GUID defined as a primary key, and an index exists for each ParentGUID table. The JOINS above should not be inherently slow for any particular reason if the keys and indexes are defined correctly.

+1
source

More articles:


All Articles