Take a spherical example with three databases:

main:

id | title ---|------ 1 | alpha 2 | beta 3 | gamma

sub1:

  id | id_main | sub1 ---|---------|------- 1 | 1 | альфа 2 | 3 | гамма1 3 | 3 | гамма2

sub2:

  id | id_main | sub2 ---|---------|-------- 1 | 1 | Άλφα 2 | 3 | Γάμμα1 3 | 3 | Γάμμα2

Offhand I will name three options to collect all the data together:

  • Make three requests in succession - on request to each table.
  • Make a query with LEFT / RIGHT JOIN, getting one type table:

(result)

 id | title | sub1 | sub2 ---|-------|--------|-------- 1 | alpha | альфа | Άλφα 2 | beta | NULL | NULL 3 | gamma | гамма1 | Γάμμα1 3 | gamma | гамма1 | Γάμμα2 3 | gamma | гамма2 | Γάμμα1 3 | gamma | гамма2 | Γάμμα2
  • Make a query with UNION SELECT to get all the data as key / value pairs:

(result)

  id | value | table ---|--------|----- 1 | alpha | main 1 | альфа | sub1 1 | Άλφα | sub2 2 | beta | main 3 | gamma | main 3 | гамма1 | sub1 3 | гамма2 | sub1 3 | Γάμμα1 | sub2 3 | Γάμμα2 | sub2

Are there any other ways to sample?

    1 answer 1

    Another spherical option.

     select main.id, main.title, sub1.id as sub_id, sub1.title as sub_title, 'sub1' as _table from main join sub1 on main.id = sub_id_main union all select main.id, main.title, sub2.id, sub2.title, 'sub2' from main join sub2 on main.id = sub_id_main

    It all depends on why you need data. The spherical version of the meaning is not considered, the physical meaning is needed

    • Cool option. Remember. Thank!! And the spherical option is to abstract from a specific case. I understand that your instrument fits a specific task, and yet I want to check if there are any more tools? - Denis Khvorostin
    • Well, you have to reason what data you are pulling out. Here it turns out that for each line from main we take n lines from sub1 (we get the link between main and sub1) and we get m lines from sub2 (the link between main and sub2). The result is m + n rows. Replays will be only for main, i.e. on them you can iterate and determine to which line from main which child value corresponds. In principle, this is a variant of the values ​​of multi-valued properties. Each user can have an arbitrary number of, say, posts and an arbitrary number of comments ... - Yura Ivanov