0

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 enter image description here

(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 ?

Improve this question
3
  • sure - thanks i mean tables : ill correct that Commented Mar 2, 2019 at 10:58
  • 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? 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 Commented Mar 2, 2019 at 12:55

2 Answers 2

Reset to default
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()))); } }
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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} } } }
@Holycrabbe yes, but you can far more easily process that array in PHP than in MySQL. See my edit for some sample code.
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

Improve this answer

5 Comments

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
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.
Btw, if you have MySql 8 then it might be easier. Because then you have the JSON_ARRAYAGG and JSON_OBJECTAGG aggregation functions.
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
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.