How to JOIN two joined subqueries for a total of a five-table JOINHow to check if a column exists in a SQL Server table?How can I do an UPDATE statement with JOIN in SQL?How to join (merge) data frames (inner, outer, left, right)Update a table using JOIN in SQL Server?SQL Server: How to Join to first rowHow to drop a table if it exists in SQL Server?How to Delete using INNER JOIN with SQL Server?Creating a materialized view filling up the complete temp spaceMYSQL - Quantity Left - Product with Serial - Multiple TableConvert multiple SQL code with multiple subqueries into a single query
Was Switzerland really impossible to invade during WW2?
Is it possible to create a golf ball sized star?
Avoiding racist tropes in fantasy
Is refusing to concede in the face of an unstoppable Nexus combo punishable?
Table caption in the middle of the table
Would it be possible to have a GMO that produces chocolate?
Script that helps people make better choices
Why we don't have vaccination against all diseases which are caused by microbes?
Why is my Earth simulation slower than the reality?
Fancy String Replace
Why didn’t Doctor Strange stay in the original winning timeline?
What to say to a student who has failed?
What does it mean to have a subnet mask /32?
Does an object count as "being moved" when placed in a Bag of Holding before its wielder moves, and then after moving they take the object out again?
On the feasibility of space battleships
Do AT motherboards (286, 386, 486) really need -5V (besides redirecting it to ISA connectors)?
What is the hex versus octal timeline?
Why don't we use Cavea-B
Why does The Ancient One think differently about Doctor Strange in Endgame than the film Doctor Strange?
Why can't an Airbus A330 dump fuel in an emergency?
How much code would a codegolf golf if a codegolf could golf code?
What professions would a medieval village with a population of 100 need?
Ask for a paid taxi in order to arrive as early as possible for an interview within the city
How is "sein" conjugated in this sub-sentence?
How to JOIN two joined subqueries for a total of a five-table JOIN
How to check if a column exists in a SQL Server table?How can I do an UPDATE statement with JOIN in SQL?How to join (merge) data frames (inner, outer, left, right)Update a table using JOIN in SQL Server?SQL Server: How to Join to first rowHow to drop a table if it exists in SQL Server?How to Delete using INNER JOIN with SQL Server?Creating a materialized view filling up the complete temp spaceMYSQL - Quantity Left - Product with Serial - Multiple TableConvert multiple SQL code with multiple subqueries into a single query
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
-1
I have two queries, each of which works perfectly and gives me the results I desire.
Query 1:
SELECT COALESCE(m.ID, r.ID, s.ID) ID,
COALESCE(m.Test, r.Test) Test,
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First, s.Last
FROM Tabl1 AS m
FULL OUTER JOIN Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
This gives me:
ID TEST M_Col1 M_Col2 M_Col3 E_Col1 E_Col2 E_Col3 First Last
101 2013-MEG3 Stuff Stuff Stuff Stuff Stuff Stuff John Smith
102 2013-MG3 Stuff Stuff Stuff Jane Doe
102 2013-EG3 Stuff Stuff Stuff Jane Doe
103 2013-MG5 Stuff Stuff Stuff Joe Smithe
103 2013-EG5 Stuff Stuff Stuff Joe Smithe
104 2013-MEG6 Stuff Stuff Stuff Stuff Stuff Stuff Jane Dawn
105 2013-MG6 Stuff Stuff Stuff Jaime Swans
106 2013-EG6 Stuff Stuff Stuff Jaime Swans
My Second Query, Query 2:
SELECT a.ID, a.Test, t.Other, t.Other_No
FROM Table4 as t
FULL OUTER JOIN Table5 as a
ON (a.Other_Key = t.Other_Key)
This gives me a similar table result.
What I want to do is JOIN these two subqueries together, basically:
(
SELECT
COALESCE(m.ID, r.ID, a.ID) ID,
COALESCE(m.Test, r.Test, a.Test) Test,
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First, s.Last
FROM
Tabl1 AS m
FULL OUTER JOIN
Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN
Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
)
q1
FULL OUTER JOIN
(
SELECT
a.ID, a.Test, t.Other, t.Other_No
FROM
Table4 as t
FULL OUTER JOIN
Table5 as a
ON (a.Other_Key = t.Other_Key)
)
q2
ON(q1.ID = q2.ID)
But this doesn't work. It just gives me the results of Query1. What am I doing wrong?
sql
sql-server join asked Mar 27 at 16:25
DrakeMurdochDrakeMurdoch
1069 bronze badges
add a comment |
-1
I have two queries, each of which works perfectly and gives me the results I desire.
Query 1:
SELECT COALESCE(m.ID, r.ID, s.ID) ID,
COALESCE(m.Test, r.Test) Test,
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First, s.Last
FROM Tabl1 AS m
FULL OUTER JOIN Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
This gives me:
ID TEST M_Col1 M_Col2 M_Col3 E_Col1 E_Col2 E_Col3 First Last
101 2013-MEG3 Stuff Stuff Stuff Stuff Stuff Stuff John Smith
102 2013-MG3 Stuff Stuff Stuff Jane Doe
102 2013-EG3 Stuff Stuff Stuff Jane Doe
103 2013-MG5 Stuff Stuff Stuff Joe Smithe
103 2013-EG5 Stuff Stuff Stuff Joe Smithe
104 2013-MEG6 Stuff Stuff Stuff Stuff Stuff Stuff Jane Dawn
105 2013-MG6 Stuff Stuff Stuff Jaime Swans
106 2013-EG6 Stuff Stuff Stuff Jaime Swans
My Second Query, Query 2:
SELECT a.ID, a.Test, t.Other, t.Other_No
FROM Table4 as t
FULL OUTER JOIN Table5 as a
ON (a.Other_Key = t.Other_Key)
This gives me a similar table result.
What I want to do is JOIN these two subqueries together, basically:
(
SELECT
COALESCE(m.ID, r.ID, a.ID) ID,
COALESCE(m.Test, r.Test, a.Test) Test,
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First, s.Last
FROM
Tabl1 AS m
FULL OUTER JOIN
Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN
Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
)
q1
FULL OUTER JOIN
(
SELECT
a.ID, a.Test, t.Other, t.Other_No
FROM
Table4 as t
FULL OUTER JOIN
Table5 as a
ON (a.Other_Key = t.Other_Key)
)
q2
ON(q1.ID = q2.ID)
But this doesn't work. It just gives me the results of Query1. What am I doing wrong?
sql
sql-server join asked Mar 27 at 16:25
DrakeMurdochDrakeMurdoch
1069 bronze badges
1
If you reformat your queries to use indentations, etc (as I've done for your final query) it's much easier to see what's going on. The first thing I notice is that it doesn't start withSELECT ... FROM. Is that a typo? Or is that the cause of your problems? (Also, as a strong principle, if you want us to debug problem code, it helps us A LOT to actually give us the error message you get.)
– MatBailie
Mar 27 at 16:52
1
Another problem is that you first query tries to read from aliasa, but you don't have a table or view aliased toain that query. So, Query1 can't possibly have run? (I think you probably meants)
– MatBailie
Mar 27 at 16:54
Please use text, not images/links, for text--including tables & ERDs. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. If you give an image, include a legend/key/explanation. Insert images/links using edit functions.
– philipxy
Mar 27 at 20:25
Please in code questions give a minimal reproducible example--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means adding minimal problem code to minimal working code. So give minimal code that you show does what you expect & minimal code with the first place you go wrong. (Debugging fundamental.) PS You don't explain or give an example of what you want. How are we to know? PS A "basically" or "essentially" or "in other words" that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly".
– philipxy
Mar 27 at 20:26
add a comment |
-1
-1
-1
I have two queries, each of which works perfectly and gives me the results I desire.
Query 1:
SELECT COALESCE(m.ID, r.ID, s.ID) ID,
COALESCE(m.Test, r.Test) Test,
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First, s.Last
FROM Tabl1 AS m
FULL OUTER JOIN Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
This gives me:
ID TEST M_Col1 M_Col2 M_Col3 E_Col1 E_Col2 E_Col3 First Last
101 2013-MEG3 Stuff Stuff Stuff Stuff Stuff Stuff John Smith
102 2013-MG3 Stuff Stuff Stuff Jane Doe
102 2013-EG3 Stuff Stuff Stuff Jane Doe
103 2013-MG5 Stuff Stuff Stuff Joe Smithe
103 2013-EG5 Stuff Stuff Stuff Joe Smithe
104 2013-MEG6 Stuff Stuff Stuff Stuff Stuff Stuff Jane Dawn
105 2013-MG6 Stuff Stuff Stuff Jaime Swans
106 2013-EG6 Stuff Stuff Stuff Jaime Swans
My Second Query, Query 2:
SELECT a.ID, a.Test, t.Other, t.Other_No
FROM Table4 as t
FULL OUTER JOIN Table5 as a
ON (a.Other_Key = t.Other_Key)
This gives me a similar table result.
What I want to do is JOIN these two subqueries together, basically:
(
SELECT
COALESCE(m.ID, r.ID, a.ID) ID,
COALESCE(m.Test, r.Test, a.Test) Test,
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First, s.Last
FROM
Tabl1 AS m
FULL OUTER JOIN
Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN
Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
)
q1
FULL OUTER JOIN
(
SELECT
a.ID, a.Test, t.Other, t.Other_No
FROM
Table4 as t
FULL OUTER JOIN
Table5 as a
ON (a.Other_Key = t.Other_Key)
)
q2
ON(q1.ID = q2.ID)
But this doesn't work. It just gives me the results of Query1. What am I doing wrong?
sql
sql-server join asked Mar 27 at 16:25
DrakeMurdochDrakeMurdoch
1069 bronze badges
I have two queries, each of which works perfectly and gives me the results I desire.
Query 1:
SELECT COALESCE(m.ID, r.ID, s.ID) ID,
COALESCE(m.Test, r.Test) Test,
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First, s.Last
FROM Tabl1 AS m
FULL OUTER JOIN Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
This gives me:
ID TEST M_Col1 M_Col2 M_Col3 E_Col1 E_Col2 E_Col3 First Last
101 2013-MEG3 Stuff Stuff Stuff Stuff Stuff Stuff John Smith
102 2013-MG3 Stuff Stuff Stuff Jane Doe
102 2013-EG3 Stuff Stuff Stuff Jane Doe
103 2013-MG5 Stuff Stuff Stuff Joe Smithe
103 2013-EG5 Stuff Stuff Stuff Joe Smithe
104 2013-MEG6 Stuff Stuff Stuff Stuff Stuff Stuff Jane Dawn
105 2013-MG6 Stuff Stuff Stuff Jaime Swans
106 2013-EG6 Stuff Stuff Stuff Jaime Swans
My Second Query, Query 2:
SELECT a.ID, a.Test, t.Other, t.Other_No
FROM Table4 as t
FULL OUTER JOIN Table5 as a
ON (a.Other_Key = t.Other_Key)
This gives me a similar table result.
What I want to do is JOIN these two subqueries together, basically:
(
SELECT
COALESCE(m.ID, r.ID, a.ID) ID,
COALESCE(m.Test, r.Test, a.Test) Test,
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First, s.Last
FROM
Tabl1 AS m
FULL OUTER JOIN
Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN
Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
)
q1
FULL OUTER JOIN
(
SELECT
a.ID, a.Test, t.Other, t.Other_No
FROM
Table4 as t
FULL OUTER JOIN
Table5 as a
ON (a.Other_Key = t.Other_Key)
)
q2
ON(q1.ID = q2.ID)
But this doesn't work. It just gives me the results of Query1. What am I doing wrong?
sql
sql-server join sql
sql-server join asked Mar 27 at 16:25
DrakeMurdochDrakeMurdoch
1069 bronze badges
asked Mar 27 at 16:25
DrakeMurdochDrakeMurdoch
1069 bronze badges
edited May 28 at 15:12
DrakeMurdoch
asked Mar 27 at 16:25
DrakeMurdochDrakeMurdoch
1069 bronze badges
asked Mar 27 at 16:25
DrakeMurdochDrakeMurdoch
1069 bronze badges
asked Mar 27 at 16:25
DrakeMurdochDrakeMurdoch
1069 bronze badges
1069 bronze badges
1
If you reformat your queries to use indentations, etc (as I've done for your final query) it's much easier to see what's going on. The first thing I notice is that it doesn't start withSELECT ... FROM. Is that a typo? Or is that the cause of your problems? (Also, as a strong principle, if you want us to debug problem code, it helps us A LOT to actually give us the error message you get.)
– MatBailie
Mar 27 at 16:52
1
Another problem is that you first query tries to read from aliasa, but you don't have a table or view aliased toain that query. So, Query1 can't possibly have run? (I think you probably meants)
– MatBailie
Mar 27 at 16:54
Please use text, not images/links, for text--including tables & ERDs. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. If you give an image, include a legend/key/explanation. Insert images/links using edit functions.
– philipxy
Mar 27 at 20:25
Please in code questions give a minimal reproducible example--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means adding minimal problem code to minimal working code. So give minimal code that you show does what you expect & minimal code with the first place you go wrong. (Debugging fundamental.) PS You don't explain or give an example of what you want. How are we to know? PS A "basically" or "essentially" or "in other words" that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly".
– philipxy
Mar 27 at 20:26
add a comment |
1
If you reformat your queries to use indentations, etc (as I've done for your final query) it's much easier to see what's going on. The first thing I notice is that it doesn't start withSELECT ... FROM. Is that a typo? Or is that the cause of your problems? (Also, as a strong principle, if you want us to debug problem code, it helps us A LOT to actually give us the error message you get.)
– MatBailie
Mar 27 at 16:52
1
Another problem is that you first query tries to read from aliasa, but you don't have a table or view aliased toain that query. So, Query1 can't possibly have run? (I think you probably meants)
– MatBailie
Mar 27 at 16:54
Please use text, not images/links, for text--including tables & ERDs. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. If you give an image, include a legend/key/explanation. Insert images/links using edit functions.
– philipxy
Mar 27 at 20:25
Please in code questions give a minimal reproducible example--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means adding minimal problem code to minimal working code. So give minimal code that you show does what you expect & minimal code with the first place you go wrong. (Debugging fundamental.) PS You don't explain or give an example of what you want. How are we to know? PS A "basically" or "essentially" or "in other words" that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly".
– philipxy
Mar 27 at 20:26
1
1
If you reformat your queries to use indentations, etc (as I've done for your final query) it's much easier to see what's going on. The first thing I notice is that it doesn't start with
SELECT ... FROM. Is that a typo? Or is that the cause of your problems? (Also, as a strong principle, if you want us to debug problem code, it helps us A LOT to actually give us the error message you get.)– MatBailie
Mar 27 at 16:52
If you reformat your queries to use indentations, etc (as I've done for your final query) it's much easier to see what's going on. The first thing I notice is that it doesn't start with
SELECT ... FROM. Is that a typo? Or is that the cause of your problems? (Also, as a strong principle, if you want us to debug problem code, it helps us A LOT to actually give us the error message you get.)– MatBailie
Mar 27 at 16:52
1
1
Another problem is that you first query tries to read from alias
a, but you don't have a table or view aliased to a in that query. So, Query1 can't possibly have run? (I think you probably meant s)– MatBailie
Mar 27 at 16:54
Another problem is that you first query tries to read from alias
a, but you don't have a table or view aliased to a in that query. So, Query1 can't possibly have run? (I think you probably meant s)– MatBailie
Mar 27 at 16:54
Please use text, not images/links, for text--including tables & ERDs. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. If you give an image, include a legend/key/explanation. Insert images/links using edit functions.
– philipxy
Mar 27 at 20:25
Please use text, not images/links, for text--including tables & ERDs. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. If you give an image, include a legend/key/explanation. Insert images/links using edit functions.
– philipxy
Mar 27 at 20:25
Please in code questions give a minimal reproducible example--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means adding minimal problem code to minimal working code. So give minimal code that you show does what you expect & minimal code with the first place you go wrong. (Debugging fundamental.) PS You don't explain or give an example of what you want. How are we to know? PS A "basically" or "essentially" or "in other words" that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly".
– philipxy
Mar 27 at 20:26
Please in code questions give a minimal reproducible example--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means adding minimal problem code to minimal working code. So give minimal code that you show does what you expect & minimal code with the first place you go wrong. (Debugging fundamental.) PS You don't explain or give an example of what you want. How are we to know? PS A "basically" or "essentially" or "in other words" that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly".
– philipxy
Mar 27 at 20:26
add a comment |
1 Answer
1
active
oldest
votes
1
Maybe assuming a lot here, but I think this may be close to what you want:
SELECT COALESCE(m.ID, r.ID, s.ID, q2.ID) ID, --added ID from query 2 to coalesce function
COALESCE(m.Test, r.Test, s.Test, q2.Test) Test, --added Test from query 2 to coalesce function
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First,
s.Last
--add q2.Other or q2.Other_No here if you want to return from query 2
FROM Tabl1 AS m
FULL OUTER JOIN Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
FULL OUTER JOIN (
SELECT b.ID, b.Test, t.Other, t.Other_No
FROM Table4 as t
FULL OUTER JOIN Table5 as a
ON (a.Other_Key = t.Other_Key)
) q2 ON COALESCE(m.ID, r.ID, s.ID) = q2.ID
answered Mar 27 at 17:00
ThermosThermos
1816 bronze badges
This works, except that it gives a bunch of rows where everything is NULL except for what'sq2.Otherandq2.Other_No. Would that be fixed with a simpleLEFT JOINinstead ofFULL OUTER JOIN?
– DrakeMurdoch
Mar 27 at 17:44
Yes - a full outer join will return every row from every table involved, regardless of whether there is a match. That's why you see so many NULLs. If you wanted to change those to inner joins, you could also probably get rid of your COALESCE functions, since you won't have to look through a bunch of nulls before you find a value.
– Thermos
Mar 27 at 17:54
Please don't answer unclear questions, comment for clarification & vote down & flag/vote to close.
– philipxy
Mar 27 at 20:33
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55382098%2fhow-to-join-two-joined-subqueries-for-a-total-of-a-five-table-join%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Maybe assuming a lot here, but I think this may be close to what you want:
SELECT COALESCE(m.ID, r.ID, s.ID, q2.ID) ID, --added ID from query 2 to coalesce function
COALESCE(m.Test, r.Test, s.Test, q2.Test) Test, --added Test from query 2 to coalesce function
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First,
s.Last
--add q2.Other or q2.Other_No here if you want to return from query 2
FROM Tabl1 AS m
FULL OUTER JOIN Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
FULL OUTER JOIN (
SELECT b.ID, b.Test, t.Other, t.Other_No
FROM Table4 as t
FULL OUTER JOIN Table5 as a
ON (a.Other_Key = t.Other_Key)
) q2 ON COALESCE(m.ID, r.ID, s.ID) = q2.ID
answered Mar 27 at 17:00
ThermosThermos
1816 bronze badges
This works, except that it gives a bunch of rows where everything is NULL except for what'sq2.Otherandq2.Other_No. Would that be fixed with a simpleLEFT JOINinstead ofFULL OUTER JOIN?
– DrakeMurdoch
Mar 27 at 17:44
Yes - a full outer join will return every row from every table involved, regardless of whether there is a match. That's why you see so many NULLs. If you wanted to change those to inner joins, you could also probably get rid of your COALESCE functions, since you won't have to look through a bunch of nulls before you find a value.
– Thermos
Mar 27 at 17:54
Please don't answer unclear questions, comment for clarification & vote down & flag/vote to close.
– philipxy
Mar 27 at 20:33
add a comment |
1
Maybe assuming a lot here, but I think this may be close to what you want:
SELECT COALESCE(m.ID, r.ID, s.ID, q2.ID) ID, --added ID from query 2 to coalesce function
COALESCE(m.Test, r.Test, s.Test, q2.Test) Test, --added Test from query 2 to coalesce function
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First,
s.Last
--add q2.Other or q2.Other_No here if you want to return from query 2
FROM Tabl1 AS m
FULL OUTER JOIN Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
FULL OUTER JOIN (
SELECT b.ID, b.Test, t.Other, t.Other_No
FROM Table4 as t
FULL OUTER JOIN Table5 as a
ON (a.Other_Key = t.Other_Key)
) q2 ON COALESCE(m.ID, r.ID, s.ID) = q2.ID
answered Mar 27 at 17:00
ThermosThermos
1816 bronze badges
This works, except that it gives a bunch of rows where everything is NULL except for what'sq2.Otherandq2.Other_No. Would that be fixed with a simpleLEFT JOINinstead ofFULL OUTER JOIN?
– DrakeMurdoch
Mar 27 at 17:44
Yes - a full outer join will return every row from every table involved, regardless of whether there is a match. That's why you see so many NULLs. If you wanted to change those to inner joins, you could also probably get rid of your COALESCE functions, since you won't have to look through a bunch of nulls before you find a value.
– Thermos
Mar 27 at 17:54
Please don't answer unclear questions, comment for clarification & vote down & flag/vote to close.
– philipxy
Mar 27 at 20:33
add a comment |
1
1
1
Maybe assuming a lot here, but I think this may be close to what you want:
SELECT COALESCE(m.ID, r.ID, s.ID, q2.ID) ID, --added ID from query 2 to coalesce function
COALESCE(m.Test, r.Test, s.Test, q2.Test) Test, --added Test from query 2 to coalesce function
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First,
s.Last
--add q2.Other or q2.Other_No here if you want to return from query 2
FROM Tabl1 AS m
FULL OUTER JOIN Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
FULL OUTER JOIN (
SELECT b.ID, b.Test, t.Other, t.Other_No
FROM Table4 as t
FULL OUTER JOIN Table5 as a
ON (a.Other_Key = t.Other_Key)
) q2 ON COALESCE(m.ID, r.ID, s.ID) = q2.ID
answered Mar 27 at 17:00
ThermosThermos
1816 bronze badges
Maybe assuming a lot here, but I think this may be close to what you want:
SELECT COALESCE(m.ID, r.ID, s.ID, q2.ID) ID, --added ID from query 2 to coalesce function
COALESCE(m.Test, r.Test, s.Test, q2.Test) Test, --added Test from query 2 to coalesce function
COALESCE(m.School, r.School, s.School) School,
m.M_Col1, m.M_Col2, m.M_Col3,
r.E_Col1, r.E_Col2, r.E_Col3,
s.First,
s.Last
--add q2.Other or q2.Other_No here if you want to return from query 2
FROM Tabl1 AS m
FULL OUTER JOIN Table2 AS r
ON(m.Test = r.Test AND m.ID = r.ID)
INNER JOIN Table3 AS s
ON COALESCE(m.ID, r.ID)= s.ID
FULL OUTER JOIN (
SELECT b.ID, b.Test, t.Other, t.Other_No
FROM Table4 as t
FULL OUTER JOIN Table5 as a
ON (a.Other_Key = t.Other_Key)
) q2 ON COALESCE(m.ID, r.ID, s.ID) = q2.ID
answered Mar 27 at 17:00
ThermosThermos
1816 bronze badges
edited Mar 27 at 17:58
answered Mar 27 at 17:00
ThermosThermos
1816 bronze badges
answered Mar 27 at 17:00
ThermosThermos
1816 bronze badges
answered Mar 27 at 17:00
ThermosThermos
1816 bronze badges
1816 bronze badges
This works, except that it gives a bunch of rows where everything is NULL except for what'sq2.Otherandq2.Other_No. Would that be fixed with a simpleLEFT JOINinstead ofFULL OUTER JOIN?
– DrakeMurdoch
Mar 27 at 17:44
Yes - a full outer join will return every row from every table involved, regardless of whether there is a match. That's why you see so many NULLs. If you wanted to change those to inner joins, you could also probably get rid of your COALESCE functions, since you won't have to look through a bunch of nulls before you find a value.
– Thermos
Mar 27 at 17:54
Please don't answer unclear questions, comment for clarification & vote down & flag/vote to close.
– philipxy
Mar 27 at 20:33
add a comment |
This works, except that it gives a bunch of rows where everything is NULL except for what'sq2.Otherandq2.Other_No. Would that be fixed with a simpleLEFT JOINinstead ofFULL OUTER JOIN?
– DrakeMurdoch
Mar 27 at 17:44
Yes - a full outer join will return every row from every table involved, regardless of whether there is a match. That's why you see so many NULLs. If you wanted to change those to inner joins, you could also probably get rid of your COALESCE functions, since you won't have to look through a bunch of nulls before you find a value.
– Thermos
Mar 27 at 17:54
Please don't answer unclear questions, comment for clarification & vote down & flag/vote to close.
– philipxy
Mar 27 at 20:33
This works, except that it gives a bunch of rows where everything is NULL except for what's
q2.Other and q2.Other_No. Would that be fixed with a simple LEFT JOIN instead of FULL OUTER JOIN?– DrakeMurdoch
Mar 27 at 17:44
This works, except that it gives a bunch of rows where everything is NULL except for what's
q2.Other and q2.Other_No. Would that be fixed with a simple LEFT JOIN instead of FULL OUTER JOIN?– DrakeMurdoch
Mar 27 at 17:44
Yes - a full outer join will return every row from every table involved, regardless of whether there is a match. That's why you see so many NULLs. If you wanted to change those to inner joins, you could also probably get rid of your COALESCE functions, since you won't have to look through a bunch of nulls before you find a value.
– Thermos
Mar 27 at 17:54
Yes - a full outer join will return every row from every table involved, regardless of whether there is a match. That's why you see so many NULLs. If you wanted to change those to inner joins, you could also probably get rid of your COALESCE functions, since you won't have to look through a bunch of nulls before you find a value.
– Thermos
Mar 27 at 17:54
Please don't answer unclear questions, comment for clarification & vote down & flag/vote to close.
– philipxy
Mar 27 at 20:33
Please don't answer unclear questions, comment for clarification & vote down & flag/vote to close.
– philipxy
Mar 27 at 20:33
add a comment |
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55382098%2fhow-to-join-two-joined-subqueries-for-a-total-of-a-five-table-join%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
If you reformat your queries to use indentations, etc (as I've done for your final query) it's much easier to see what's going on. The first thing I notice is that it doesn't start with
SELECT ... FROM. Is that a typo? Or is that the cause of your problems? (Also, as a strong principle, if you want us to debug problem code, it helps us A LOT to actually give us the error message you get.)– MatBailie
Mar 27 at 16:52
1
Another problem is that you first query tries to read from alias
a, but you don't have a table or view aliased toain that query. So, Query1 can't possibly have run? (I think you probably meants)– MatBailie
Mar 27 at 16:54
Please use text, not images/links, for text--including tables & ERDs. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. If you give an image, include a legend/key/explanation. Insert images/links using edit functions.
– philipxy
Mar 27 at 20:25
Please in code questions give a minimal reproducible example--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means adding minimal problem code to minimal working code. So give minimal code that you show does what you expect & minimal code with the first place you go wrong. (Debugging fundamental.) PS You don't explain or give an example of what you want. How are we to know? PS A "basically" or "essentially" or "in other words" that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly".
– philipxy
Mar 27 at 20:26