2

EDIT: An extension to this question can be found here
I want to join four different tables with SQLite which have just two columns in common. Please take a look at following example

+--------+---+-----+-----+
| table1 |   |     |     |
+--------+---+-----+-----+
| a      | b | lon | lat |
+--------+---+-----+-----+
| 1      | 2 | 111 | 222 |
+--------+---+-----+-----+
+--------+---+-----+-----+
| table2 |   |     |     |
+--------+---+-----+-----+
| c      | d | lon | lat |
+--------+---+-----+-----+
| 3      | 4 | 333 | 444 |
+--------+---+-----+-----+
+--------+---+-----+-----+
| table3 |   |     |     |
+--------+---+-----+-----+
| e      | f | lon | lat |
+--------+---+-----+-----+
| 5      | 6 | 555 | 666 |
+--------+---+-----+-----+
+--------+---+-----+-----+
| table4 |   |     |     |
+--------+---+-----+-----+
| g      | h | lon | lat |
+--------+---+-----+-----+
| 7      | 8 | 777 | 888 |
+--------+---+-----+-----+

The tables are not connected by any foreign key. Also, the lon/lat values are different for every row. An optimal output would be:

+------+------+------+------+------+------+------+------+-----+-----+
|  a   |  b   |  c   |  d   |  e   |  f   |  g   |  h   | lon | lat |
+------+------+------+------+------+------+------+------+-----+-----+
| None | None | 3    | 4    | None | None | None | NOne | 333 | 444 |
| 1    | 2    | None | None | None | None | None | None | 111 | 222 |
| None | None | None | None | 5    | 6    | None | None | 555 | 666 |
| None | None | None | None | None | None | 7    | 8    | 777 | 888 |
+------+------+------+------+------+------+------+------+-----+-----+
  • Again, this is my end goal - lat/lon values are not interconnected between the tables:
    • a | b | c | .. | lat | lon | instead of
    • a | b | c | .. | table1.lat | table2.lat | ...
  • Appreciate your help!
  • My current code 
-- First two tables
CREATE VIEW ab AS
SELECT * FROM table1 LEFT JOIN table2 ON ???
UNION ALL
SELECT * FROM table2 LEFT JOIN table1 ON ?? WHERE ?? IS NULL

-- 3rd and 4th table
CREATE VIEW cd AS
SELECT * FROM table3 LEFT JOIN table4 ON ??
UNION ALL
SELECT * FROM table4 LEFT JOIN table3 ON ?? WHERE ?? IS NULL

-- -- JOIN
SELECT * FROM cd LEFT JOIN ab ON ??
UNION ALL
SELECT * FROM cd LEFT JOIN ab ON ?? WHERE ?? IS NULL
AndrejCoding
  • 127
  • 9
  • if you don't want user.longitude, university.longitude, etc. then which values will be there in latitude and longitude? From where they will come from? – Jinesh Shah Dec 06 '17 at 03:38
  • @JINESHSHAH I do want to have longitude and latitude on my table but **just once** (see the example). I do not want to have user.longitude, university.longitude, etc. on it. Am I expressing myself concisely? a | b | c | .. | latitude | longitude | instead of a | b | c | .. | user.latitude | university.latitude | ... – AndrejCoding Dec 06 '17 at 04:15
  • @AndrejCoding: Kindly share sample data from which you derived the sample desired output – zarruq Dec 06 '17 at 04:17
  • @zarruq What do you mean? :) I created the tables by myself and entered some dummy values to test whether the code works out. How/What should I exactly share? – AndrejCoding Dec 06 '17 at 04:19
  • @AndrejCoding: You just share your desired output but from which data is it derived exactly? Your sample table1, table2, table3 and table4 has no data.. – zarruq Dec 06 '17 at 04:22
  • @zarruq better now? THank you I saw your post! Let me check – AndrejCoding Dec 06 '17 at 04:34
  • @AndrejCoding: check my answer :-) – zarruq Dec 06 '17 at 04:35

1 Answers1

3

Assuming that your sample tables has data like below

Table1:

a   b   lon lat
---------------
22  33  11  22

Table2:

c   d   lon lat
---------------
1   2   44  45

Table3

e       f       lon lat
-----------------------
NULL    NULL    100 101

Table4

g       h       lon lat
-----------------------
NULL    NULL    200 201

and you want to merge the records, you can use union all.

select a,b,NULL as c, NULL as d,NULL as e, NULL as f, NULL as g, NULL as h, lon,lat
from table1
union all
select NULL, NULL,c,d,NULL as e, NULL as f, NULL as g, NULL as h, lon,lat
from table2
union all
select NULL, NULL,NULL,NULL,e,f, NULL as g, NULL as h, lon,lat
from table3
union all
select NULL, NULL,NULL,NULL,NULL,NULL,g,h, lon,lat
from table4

Result:

+------+------+------+------+------+------+------+------+-----+-----+
|  a   |  b   |  c   |  d   |  e   |  f   |  g   |  h   | lon | lat |
+------+------+------+------+------+------+------+------+-----+-----+
| 22   | 33   | NULL | NULL | NULL | NULL | NULL | NULL |  11 |  22 |
| NULL | NULL | 1    | 2    | NULL | NULL | NULL | NULL |  44 |  45 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 100 | 101 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 200 | 201 |
+------+------+------+------+------+------+------+------+-----+-----+

DEMO

zarruq
  • 2,445
  • 2
  • 10
  • 19
  • Thank you!!!! However, what if I have not just the columns a until h, but a until z, i.e many many columns in table1, table2, table3, and table4 -> It would be very time-consuming and the structure wouldn't be clear if I had to write everywhere NULL as [letter] in my sql statement -> Is there another approach? @zarruq – AndrejCoding Dec 06 '17 at 04:38
  • @AndrejCoding: In that case you can just use `union all` and then `pivot` to convert `columns` to `rows` :-) – zarruq Dec 06 '17 at 04:42
  • YOU ARE GREAT!!! :) Last question: Can you help me out and show it in your demo code? I have never used pivot... @zarruq – AndrejCoding Dec 06 '17 at 04:45
  • @AndrejCoding: For future users convenience, Kindly close this question and add new one as per your requirement referencing the current question :-). We will be happy to help you with that also. Thanks! – zarruq Dec 06 '17 at 04:47
  • Extension can be found here [link](https://stackoverflow.com/questions/47667304/sqlite-full-outer-join-with-four-tables-with-30-columns) – AndrejCoding Dec 06 '17 at 05:43