SQL: query related data from multiple tables

Question

I have 4 tables, who all relate to each other in such a way that: TB1 <- TB2 <- TB3 <- TB4 meaning that TB4 holds elements that belongs to a single row in TB3, TB3 holds elements that belongs to a single row in TB2 and finally TB2 holds data that belongs to a single row in TB1. i made this illustration to try to make it more clear

(edit: DB was suppose to be TB as in table)

I have tried to achieve this by using subqueries as follows:

SELECT TB1.id AS TB1_ID, 
    (SELECT TB2.id AS TB2_ID,
        (SELECT TB3.id AS TB3_ID,
            (SELECT TB4.id AS TB4_ID
            FROM `TB4` AS TB4 WHERE TB4.TB3_id = TB3.id) AS C
        FROM `TB3` AS TB3 WHERE TB3.TB2_id = TB2.id) AS B
    FROM `TB2` AS TB2 WHERE TB2.TB1_id = TB1.id) AS A
FROM `TB1` AS TB1

yet my logic must be flawed: or there is something i am missing about querying related data: as this returns null, even though i know that the tables holds the necessary informations needed to make such a cross combination.

The desired result is a set of nested arrays within an array: one nested array for each tables. so that we ends up with a structure like:

{*, A{*, B{*, C{*} } } }

so that each row from TB1 contains a multidimensional array of elements from TB2 as a variable and each row form TB2 contains a multidimensional array of elements from TB3 as an element and so on...

I have also tried to pull all information as separate queries and then joining them in JS, however turned out to be quit heavy: so i would truly appreciate if anyone knew how to do this in a proper way - thanks a lot in advance

PS. im trying it in my local environment, through use of XAMPP: does this create a problem ?

The way to do this may very well depend on the version of MySql. See here for example. So which version of MySql do you have to work with? — LukStorms
– LukStorms, Commented Mar 2, 2019 at 11:18
thats a really good question - yet i cant figure out how to find out what version of Mysql that XAMPP 7.1.7 runs — Holycrabbe
– Holycrabbe, Commented Mar 2, 2019 at 12:55

Nick · Accepted Answer · 2019-03-02 22:09:17Z

2

I think what you want is a series of JOINs:

SELECT TB1.id AS TB1_ID, TB2.id AS TB2_ID, TB3.id AS TB3_ID, TB4.id AS TB4_ID
FROM TB1
JOIN TB2 ON TB2.TB1_ID = TB1.ID
JOIN TB3 ON TB3.TB2_ID = TB2.ID
JOIN TB4 ON TB4.TB3_ID = TB3.ID

You can then build your desired structure in PHP using something like:

$sql = "SELECT TB1.id AS TB1_ID, TB2.id AS TB2_ID, TB3.id AS TB3_ID, TB4.id AS TB4_ID
FROM TB1
JOIN TB2 ON TB2.TB1_ID = TB1.ID
JOIN TB3 ON TB3.TB2_ID = TB2.ID
JOIN TB4 ON TB4.TB3_ID = TB3.ID";
$result = $conn->query($sql) or die($conn->error);
$output = array();
while ($row = $result->fetch_assoc()) {
    $tb1_id = $row['TB1_ID'];
    $tb2_id = $row['TB2_ID'];
    $tb3_id = $row['TB3_ID'];
    $tb4_id = $row['TB4_ID'];
    if (isset($output[$tb1_id][$tb2_id][$tb3_id])) {
        $output[$tb1_id][$tb2_id][$tb3_id][$tb4_id] = array();
    }
    elseif (isset($output[$tb1_id][$tb2_id])) {
        $output[$tb1_id][$tb2_id][$tb3_id] = array($tb4_id => array());
    }
    elseif (isset($output[$tb1_id])) {
        $output[$tb1_id][$tb2_id] = array($tb3_id => array($tb4_id => array()));
    }
    else {
        $output[$tb1_id] = array($tb2_id => array($tb3_id => array($tb4_id => array())));
    }
}

edited Mar 2, 2019 at 22:09

answered Mar 2, 2019 at 11:19

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Holycrabbe Over a year ago

But join wouldn't it just give me one long array of values? The struktur i am looking for could be exsplained as a family Having a table with family names, a table with adults and a table with children: then what i am looking for is a pull that knits together the familie like family X consists of adult A and B where adult A has children 1, 2 and 3 and adult B has children 4 and 5 : giving us a structure like {Family name, Adults{ {A, children{1,2,3}} , {B, children{4,5} } } }

Nick Over a year ago

@Holycrabbe yes, but you can far more easily process that array in PHP than in MySQL. See my edit for some sample code.

LukStorms · Accepted Answer · 2019-03-02 16:42:04Z

1

Here is an attempt that uses JSON functions and other stuff that should work in the latest 5.7 version.

But don't ask about it, because I discovered from this experiment that dealing with nested json's in MySql 5.7 is a real PITA.

Sample data:

drop table if exists Table1;
drop table if exists Table2;
drop table if exists Table3;
drop table if exists Table4;

create table Table1 (id int primary key, col1 varchar(30));
create table Table2 (id int primary key, tbl1_id int, col1 varchar(30));
create table Table3 (id int primary key, tbl2_id int, col1 varchar(30));
create table Table4 (id int primary key, tbl3_id int, col1 varchar(30));

insert into Table1 (id, col1) values
(101, 'A1'),(102, 'A2'),(103, 'A3'),(104, 'A4');

insert into Table2 (id, tbl1_id, col1) values
(201, 101, 'B1'), (202, 102, 'B2'),(203, 103, 'B3');

insert into Table3 (id, tbl2_id, col1) values
(301, 201, 'C1'),(302, 202, 'C2');

insert into Table4 (id, tbl3_id, col1) values
(401, 301, 'D1'), (402, 301, 'D2');

Query:

SELECT t1.id AS t1id, 
GROUP_CONCAT(REPLACE(JSON_OBJECT(t1.id, JSON_ARRAY(t1.col1)),']}',', '),
IFNULL(
(
  SELECT 
  GROUP_CONCAT(
   REPLACE(JSON_OBJECT(t2.id, JSON_ARRAY(t2.col1)),']}',', '),
   IFNULL(
   (
    SELECT 
    GROUP_CONCAT(
     REPLACE(JSON_OBJECT(t3.id, JSON_ARRAY(t3.col1)),']}',', '),
     IFNULL(
     (
       SELECT 
       CONCAT('[', 
        IFNULL(GROUP_CONCAT(JSON_OBJECT(t4.id, JSON_ARRAY(t4.col1))),''), 
        ']') D
       FROM Table4 t4
       WHERE t4.tbl3_id = t3.id
       GROUP BY t4.tbl3_id
     ), '[]'), ']}') C
    FROM Table3 t3
    WHERE t3.tbl2_id = t2.id
    GROUP BY t3.tbl2_id
   ), '[]'), ']}') B
  FROM Table2 t2
  WHERE t2.tbl1_id = t1.id
  GROUP BY t2.tbl1_id
 ), '[]'), ']}') A
FROM Table1 t1
GROUP BY t1.id;

Returns:

id  A
101 {"101": ["A1", {"201": ["B1", {"301": ["C1", [{"401": ["D1"]},{"402": ["D2"]}]]}]}]}
102 {"102": ["A2", {"202": ["B2", {"302": ["C2", []]}]}]}
103 {"103": ["A3", {"203": ["B3", []]}]}
104 {"104": ["A4", []]}

A test on db<>fiddle here

edited Mar 2, 2019 at 16:42

answered Mar 2, 2019 at 14:30

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

5 Comments

Holycrabbe Over a year ago

what program would i need to have, to testrun this ? im only used to using SQL through executing it in php - sorry im a bit basic here

LukStorms Over a year ago

There are tools to connect to a MySql database and run SQL on it. For example: MySql Workbench. And what's sometimes used in StackOverflow answers to demonstrate are those free online testers. F.e. rextester or dbfiddle.uk.

LukStorms Over a year ago

Btw, if you have MySql 8 then it might be easier. Because then you have the JSON_ARRAYAGG and JSON_OBJECTAGG aggregation functions.

Holycrabbe Over a year ago

thanks a lot for you answers - and technically you are right, really interesting stuff: but with it being so complicated i went with the answer of @Nick as it solves my problem in what i find to be a better way

LukStorms Over a year ago

Alright. That might be a bit cleaner than making MySql suffer over it. And I guess with those INNER JOIN's (instead of LEFT JOIN's) in that answer, it seems like you were only interesting in those that have both A & B & C & D anyway.

Collectives™ on Stack Overflow

SQL: query related data from multiple tables

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related