I have data in multiple tables I need to cross join via different attributes to achieve the output. i.e.
| ID | Node |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | G |
| ID | ParentID |
|---|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
| 5 | 500 |
| 6 | 600 |
| 7 | 100 |
| 8 | 200 |
| 9 | 300 |
| 10 | 700 |
| 11 | 800 |
| 12 | 800 |
| ID | Splice Name |
|---|---|
| 7 | Irvine |
| 8 | Goodyear |
| 9 | Phoenix |
| 10 | Seattle |
| 11 | Augusta |
| 12 | Atlanta |
Every table has a unique Child ID which corresponds to a Parent ID that is NOT unique. Multiple child IDs can be associated to the same Parent ID (as you can see above, child ID 1 and 7 for instance share Parent ID 100. The ID in each table corresponds with a unique Child ID not shared with any other tables.
What I want to do in my query is have the Nodes show what their Splice Name is. So I want to do select Node, Splice_Name. Expected output for instance would show Node A as having Splice Name Irvine. The example above is only 3 tables and a few rows but I'm working with big data (500 tables and over a million rows)
My question is, how do I write my query to do multiple cross joins? Also my example above is crossing over through one table to get data from another, but how would you cross through multiple tables?
CodePudding user response：
You do not want to CROSS JOIN. Instead, you can use INNER JOINs:
SELECT n.node, s.splice_name
FROM (
relationships rn
INNER JOIN nodes n
ON (rn.id = n.id)
)
INNER JOIN
(
relationships rs
INNER JOIN splices s
ON (rs.id = s.id)
)
ON (rn.parentid = rs.parentid);
Note: The braces are not required here, I just find they add clarity to the precedence of the joins.
Which, for the sample data:
CREATE TABLE nodes (ID, Node) As
SELECT 1, 'A' FROM DUAL UNION ALL
SELECT 2, 'B' FROM DUAL UNION ALL
SELECT 3, 'C' FROM DUAL UNION ALL
SELECT 4, 'D' FROM DUAL UNION ALL
SELECT 5, 'E' FROM DUAL UNION ALL
SELECT 6, 'G' FROM DUAL;
CREATE TABLE relationships (ID, ParentID) AS
SELECT 1, 100 FROM DUAL UNION ALL
SELECT 2, 200 FROM DUAL UNION ALL
SELECT 3, 300 FROM DUAL UNION ALL
SELECT 4, 400 FROM DUAL UNION ALL
SELECT 5, 500 FROM DUAL UNION ALL
SELECT 6, 600 FROM DUAL UNION ALL
SELECT 7, 100 FROM DUAL UNION ALL
SELECT 8, 200 FROM DUAL UNION ALL
SELECT 9, 300 FROM DUAL UNION ALL
SELECT 10, 700 FROM DUAL UNION ALL
SELECT 11, 800 FROM DUAL UNION ALL
SELECT 12, 800 FROM DUAL;
CREATE TABLE splices (ID, Splice_Name) AS
SELECT 7, 'Irvine' FROM DUAL UNION ALL
SELECT 8, 'Goodyear' FROM DUAL UNION ALL
SELECT 9, 'Phoenix' FROM DUAL UNION ALL
SELECT 10, 'Seattle' FROM DUAL UNION ALL
SELECT 11, 'Augusta' FROM DUAL UNION ALL
SELECT 12, 'Atlanta' FROM DUAL;
Outputs:
| NODE | SPLICE_NAME |
|---|---|
| A | Irvine |
| B | Goodyear |
| C | Phoenix |
If you want all the nodes and splices, including those without a corresponding relationship then use a FULL OUTER JOIN:
SELECT n.node, s.splice_name
FROM (
relationships rn
INNER JOIN nodes n
ON (rn.id = n.id)
)
FULL OUTER JOIN
(
relationships rs
INNER JOIN splices s
ON (rs.id = s.id)
)
ON (rn.parentid = rs.parentid)
Which outputs:
| NODE | SPLICE_NAME |
|---|---|
| A | Irvine |
| B | Goodyear |
| C | Phoenix |
| (null) | Seattle |
| (null) | Augusta |
| (null) | Atlanta |
| D | (null) |
| G | (null) |
| E | (null) |
CodePudding user response：
Maybe you could consider the solution based on Left Joins.
WITH
nodes AS
(
Select 1 "ID", 'A' "NODE" From Dual Union All
Select 2 "ID", 'B' "NODE" From Dual Union All
Select 3 "ID", 'C' "NODE" From Dual Union All
Select 4 "ID", 'D' "NODE" From Dual Union All
Select 5 "ID", 'E' "NODE" From Dual Union All
Select 6 "ID", 'F' "NODE" From Dual
),
parents AS
(
Select 1 "ID", 100 "PARENT_ID" From Dual Union All
Select 2 "ID", 200 "PARENT_ID" From Dual Union All
Select 3 "ID", 300 "PARENT_ID" From Dual Union All
Select 4 "ID", 400 "PARENT_ID" From Dual Union All
Select 5 "ID", 500 "PARENT_ID" From Dual Union All
Select 6 "ID", 600 "PARENT_ID" From Dual Union All
Select 7 "ID", 100 "PARENT_ID" From Dual Union All
Select 8 "ID", 200 "PARENT_ID" From Dual Union All
Select 9 "ID", 300 "PARENT_ID" From Dual Union All
Select 10 "ID", 700 "PARENT_ID" From Dual Union All
Select 11 "ID", 800 "PARENT_ID" From Dual Union All
Select 12 "ID", 800 "PARENT_ID" From Dual
),
splices AS
(
Select 7 "ID", 'Irwine' "SPLICE_NAME" From Dual Union All
Select 8 "ID", 'Goodyear' "SPLICE_NAME" From Dual Union All
Select 9 "ID", 'Phoenix' "SPLICE_NAME" From Dual Union All
Select 10 "ID", 'Seattle' "SPLICE_NAME" From Dual Union All
Select 11 "ID", 'Augusta' "SPLICE_NAME" From Dual Union All
Select 12 "ID", 'Atlanta' "SPLICE_NAME" From Dual
)
Select
p.PARENT_ID,
n.ID "NODE_ID", n.NODE,
p.SPLICE_ID, p.ID "ID",
CASE WHEN p.SPLICE_ID Is Null Then p.SPLICE_NAME
ELSE (Select SPLICE_NAME From splices Where ID = p.SPLICE_ID)
END "SPLICE_NAME"
From
(
Select
p.PARENT_ID, p.ID "ID",
p.SPLICE_ID "SPLICE_ID", p.SPLICE_NAME "SPLICE_NM", Max(p.SPLICE_NAME) OVER(Partition By PARENT_ID) "SPLICE_NAME"
From
(
Select p1.PARENT_ID, p1.ID, s1.ID "SPLICE_ID", s1.SPLICE_NAME
From parents p1
Left Join splices s1 ON(s1.ID = p1.ID)
)p
) p
Left Join nodes n ON(n.ID = p.ID)
Order By p.ID
/*
PARENT_ID NODE_ID NODE SPLICE_ID ID SPLICE_NAME
---------- ---------- ---- ---------- ---------- -----------
100 1 A 1 Irwine
200 2 B 2 Goodyear
300 3 C 3 Phoenix
400 4 D 4
500 5 E 5
600 6 F 6
100 7 7 Irwine
200 8 8 Goodyear
300 9 9 Phoenix
700 10 10 Seattle
800 11 11 Augusta
800 12 12 Atlanta
*/
The resulting dataset could be seelected/filtered/grouped/ordered or whatever you want...
Additionaly:
if there is another table with node data like here:
nodes_2 AS
(
Select 7 "ID", 'G' "NODE" From Dual Union All
Select 8 "ID", 'H' "NODE" From Dual
),
... then just change last Left Join to union of nodes and nodes_2 (_3, _4, _n). The same could be done for splices (Left Join in inner query).. and the result would be:
PARENT_ID NODE_ID NODE SPLICE_ID ID SPLICE_NAME
---------- ---------- ---- ---------- ---------- -----------
100 1 A 1 Irwine
200 2 B 2 Goodyear
300 3 C 3 Phoenix
400 4 D 4
500 5 E 5
600 6 F 6
100 7 G 7 7 Irwine
200 8 H 8 8 Goodyear
300 9 9 Phoenix
700 10 10 Seattle
800 11 11 Augusta
800 12 12 Atlanta