Using query result field as database name in same query
0
Probably a long shot with this...
I have two databases "Job Register" and "Job001".
I want to retrieve a list of the jobs that have had a meeting, and the last time the meeting took place (which is determined by the latest revision of a certain document).
The list of jobs that have had a meeting is easy enough as they are in the same database and have a specific column for this information. The tricky bit is then to have an additional column retrieved that uses the retrieved job number to JOIN that job's database and check a document number "DOC001" in this case for it's latest revision and retrieve the "date_modified".
Please see the attached image of the two databases and the desired query result.
sql
sql-server tsql edited Dec 3 '18 at 1:45
Dale Burrell
2,98232348
asked Nov 24 '18 at 15:47
squatmansquatman
225
|
show 2 more comments
0
Probably a long shot with this...
I have two databases "Job Register" and "Job001".
I want to retrieve a list of the jobs that have had a meeting, and the last time the meeting took place (which is determined by the latest revision of a certain document).
The list of jobs that have had a meeting is easy enough as they are in the same database and have a specific column for this information. The tricky bit is then to have an additional column retrieved that uses the retrieved job number to JOIN that job's database and check a document number "DOC001" in this case for it's latest revision and retrieve the "date_modified".
Please see the attached image of the two databases and the desired query result.
sql
sql-server tsql edited Dec 3 '18 at 1:45
Dale Burrell
2,98232348
asked Nov 24 '18 at 15:47
squatmansquatman
225
3
It is not clear to me how we can even join these two tables together. You might want to explain that better.
– Tim Biegeleisen
Nov 24 '18 at 15:52
1
Database <> Table. Those are tables, not databases. Tables are just one type of object that a database can contain.
– Larnu
Nov 24 '18 at 15:55
1
I guess by JobNumber, but why in the first table the datatype is varchar and for the second is INT?
– Sami
Nov 24 '18 at 15:56
So, you have aJob002andJob003, etc table? If so, this screams bad design. The better solution here is to fix your design. Is this something that you are able to do?
– Larnu
Nov 24 '18 at 15:57
Also, why is the value fordate_modifiedin your Query results05/11/2018? The latest date in yourJob001table is10/11/2018; should the value not therefore be that?
– Larnu
Nov 24 '18 at 16:05
|
show 2 more comments
0
0
0
Probably a long shot with this...
I have two databases "Job Register" and "Job001".
I want to retrieve a list of the jobs that have had a meeting, and the last time the meeting took place (which is determined by the latest revision of a certain document).
The list of jobs that have had a meeting is easy enough as they are in the same database and have a specific column for this information. The tricky bit is then to have an additional column retrieved that uses the retrieved job number to JOIN that job's database and check a document number "DOC001" in this case for it's latest revision and retrieve the "date_modified".
Please see the attached image of the two databases and the desired query result.
sql
sql-server tsql edited Dec 3 '18 at 1:45
Dale Burrell
2,98232348
asked Nov 24 '18 at 15:47
squatmansquatman
225
Probably a long shot with this...
I have two databases "Job Register" and "Job001".
I want to retrieve a list of the jobs that have had a meeting, and the last time the meeting took place (which is determined by the latest revision of a certain document).
The list of jobs that have had a meeting is easy enough as they are in the same database and have a specific column for this information. The tricky bit is then to have an additional column retrieved that uses the retrieved job number to JOIN that job's database and check a document number "DOC001" in this case for it's latest revision and retrieve the "date_modified".
Please see the attached image of the two databases and the desired query result.
sql
sql-server tsql sql
sql-server tsql edited Dec 3 '18 at 1:45
Dale Burrell
2,98232348
asked Nov 24 '18 at 15:47
squatmansquatman
225
edited Dec 3 '18 at 1:45
Dale Burrell
2,98232348
asked Nov 24 '18 at 15:47
squatmansquatman
225
edited Dec 3 '18 at 1:45
Dale Burrell
2,98232348
edited Dec 3 '18 at 1:45
Dale Burrell
2,98232348
edited Dec 3 '18 at 1:45
Dale Burrell
2,98232348
2,98232348
asked Nov 24 '18 at 15:47
squatmansquatman
225
asked Nov 24 '18 at 15:47
squatmansquatman
225
asked Nov 24 '18 at 15:47
squatmansquatman
225
225
3
It is not clear to me how we can even join these two tables together. You might want to explain that better.
– Tim Biegeleisen
Nov 24 '18 at 15:52
1
Database <> Table. Those are tables, not databases. Tables are just one type of object that a database can contain.
– Larnu
Nov 24 '18 at 15:55
1
I guess by JobNumber, but why in the first table the datatype is varchar and for the second is INT?
– Sami
Nov 24 '18 at 15:56
So, you have aJob002andJob003, etc table? If so, this screams bad design. The better solution here is to fix your design. Is this something that you are able to do?
– Larnu
Nov 24 '18 at 15:57
Also, why is the value fordate_modifiedin your Query results05/11/2018? The latest date in yourJob001table is10/11/2018; should the value not therefore be that?
– Larnu
Nov 24 '18 at 16:05
|
show 2 more comments
3
It is not clear to me how we can even join these two tables together. You might want to explain that better.
– Tim Biegeleisen
Nov 24 '18 at 15:52
1
Database <> Table. Those are tables, not databases. Tables are just one type of object that a database can contain.
– Larnu
Nov 24 '18 at 15:55
1
I guess by JobNumber, but why in the first table the datatype is varchar and for the second is INT?
– Sami
Nov 24 '18 at 15:56
So, you have aJob002andJob003, etc table? If so, this screams bad design. The better solution here is to fix your design. Is this something that you are able to do?
– Larnu
Nov 24 '18 at 15:57
Also, why is the value fordate_modifiedin your Query results05/11/2018? The latest date in yourJob001table is10/11/2018; should the value not therefore be that?
– Larnu
Nov 24 '18 at 16:05
3
3
It is not clear to me how we can even join these two tables together. You might want to explain that better.
– Tim Biegeleisen
Nov 24 '18 at 15:52
It is not clear to me how we can even join these two tables together. You might want to explain that better.
– Tim Biegeleisen
Nov 24 '18 at 15:52
1
1
Database <> Table. Those are tables, not databases. Tables are just one type of object that a database can contain.
– Larnu
Nov 24 '18 at 15:55
Database <> Table. Those are tables, not databases. Tables are just one type of object that a database can contain.
– Larnu
Nov 24 '18 at 15:55
1
1
I guess by JobNumber, but why in the first table the datatype is varchar and for the second is INT?
– Sami
Nov 24 '18 at 15:56
I guess by JobNumber, but why in the first table the datatype is varchar and for the second is INT?
– Sami
Nov 24 '18 at 15:56
So, you have a
Job002 and Job003, etc table? If so, this screams bad design. The better solution here is to fix your design. Is this something that you are able to do?– Larnu
Nov 24 '18 at 15:57
So, you have a
Job002 and Job003, etc table? If so, this screams bad design. The better solution here is to fix your design. Is this something that you are able to do?– Larnu
Nov 24 '18 at 15:57
Also, why is the value for
date_modified in your Query results 05/11/2018? The latest date in your Job001 table is 10/11/2018; should the value not therefore be that?– Larnu
Nov 24 '18 at 16:05
Also, why is the value for
date_modified in your Query results 05/11/2018? The latest date in your Job001 table is 10/11/2018; should the value not therefore be that?– Larnu
Nov 24 '18 at 16:05
|
show 2 more comments
2 Answers
2
active
oldest
votes
0
Your question is unclear a bit, but I think you are looking for
SELECT JR.JobNumber,
JR.IsComplete,
J.DateModified
FROM JobRegister JR INNER JOIN Job001 J
ON JR.JobNumber = J.JobNumber
WHERE DocumentRevision = 'B';
Also JobNumber in both tables should be the same datatype.
Demo
answered Nov 24 '18 at 16:11
SamiSami
8,58331240
Sami, thank you very much for taking the time to do this. This is exactly what I want, but where you have "INNER JOIN Job001 J" I need this to be dynamic based on the value retrieved in "JR.JobNumber". This probably isn't even possible, but I thought I'd ask. so something like:
– squatman
Nov 24 '18 at 17:48
Please see response as an answer.
– squatman
Nov 24 '18 at 17:59
@squatman That's a very bad idea, you will need to use DynamicSQL
– Sami
Nov 24 '18 at 18:10
Ok Sami, thanks for your help. I guess for the time being I will have to get the result with some programming in the application. Thanks.
– squatman
Nov 24 '18 at 18:15
@squatman How would you know which Job table to join with? Job001? Job002?...etc
– Sami
Nov 24 '18 at 18:17
|
show 3 more comments
-1
SELECT JR.JobNumber,
JR.IsComplete,
J.DateModified
FROM JobRegister JR INNER JOIN CONCAT('Job', JR.JobNumber) J
ON JR.JobNumber = J.JobNumber
WHERE DocumentRevision = 'B';
answered Nov 24 '18 at 17:52
squatmansquatman
225
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%2f53459803%2fusing-query-result-field-as-database-name-in-same-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Your question is unclear a bit, but I think you are looking for
SELECT JR.JobNumber,
JR.IsComplete,
J.DateModified
FROM JobRegister JR INNER JOIN Job001 J
ON JR.JobNumber = J.JobNumber
WHERE DocumentRevision = 'B';
Also JobNumber in both tables should be the same datatype.
Demo
answered Nov 24 '18 at 16:11
SamiSami
8,58331240
Sami, thank you very much for taking the time to do this. This is exactly what I want, but where you have "INNER JOIN Job001 J" I need this to be dynamic based on the value retrieved in "JR.JobNumber". This probably isn't even possible, but I thought I'd ask. so something like:
– squatman
Nov 24 '18 at 17:48
Please see response as an answer.
– squatman
Nov 24 '18 at 17:59
@squatman That's a very bad idea, you will need to use DynamicSQL
– Sami
Nov 24 '18 at 18:10
Ok Sami, thanks for your help. I guess for the time being I will have to get the result with some programming in the application. Thanks.
– squatman
Nov 24 '18 at 18:15
@squatman How would you know which Job table to join with? Job001? Job002?...etc
– Sami
Nov 24 '18 at 18:17
|
show 3 more comments
0
Your question is unclear a bit, but I think you are looking for
SELECT JR.JobNumber,
JR.IsComplete,
J.DateModified
FROM JobRegister JR INNER JOIN Job001 J
ON JR.JobNumber = J.JobNumber
WHERE DocumentRevision = 'B';
Also JobNumber in both tables should be the same datatype.
Demo
answered Nov 24 '18 at 16:11
SamiSami
8,58331240
Sami, thank you very much for taking the time to do this. This is exactly what I want, but where you have "INNER JOIN Job001 J" I need this to be dynamic based on the value retrieved in "JR.JobNumber". This probably isn't even possible, but I thought I'd ask. so something like:
– squatman
Nov 24 '18 at 17:48
Please see response as an answer.
– squatman
Nov 24 '18 at 17:59
@squatman That's a very bad idea, you will need to use DynamicSQL
– Sami
Nov 24 '18 at 18:10
Ok Sami, thanks for your help. I guess for the time being I will have to get the result with some programming in the application. Thanks.
– squatman
Nov 24 '18 at 18:15
@squatman How would you know which Job table to join with? Job001? Job002?...etc
– Sami
Nov 24 '18 at 18:17
|
show 3 more comments
0
0
0
Your question is unclear a bit, but I think you are looking for
SELECT JR.JobNumber,
JR.IsComplete,
J.DateModified
FROM JobRegister JR INNER JOIN Job001 J
ON JR.JobNumber = J.JobNumber
WHERE DocumentRevision = 'B';
Also JobNumber in both tables should be the same datatype.
Demo
answered Nov 24 '18 at 16:11
SamiSami
8,58331240
Your question is unclear a bit, but I think you are looking for
SELECT JR.JobNumber,
JR.IsComplete,
J.DateModified
FROM JobRegister JR INNER JOIN Job001 J
ON JR.JobNumber = J.JobNumber
WHERE DocumentRevision = 'B';
Also JobNumber in both tables should be the same datatype.
Demo
answered Nov 24 '18 at 16:11
SamiSami
8,58331240
answered Nov 24 '18 at 16:11
SamiSami
8,58331240
answered Nov 24 '18 at 16:11
SamiSami
8,58331240
answered Nov 24 '18 at 16:11
SamiSami
8,58331240
8,58331240
Sami, thank you very much for taking the time to do this. This is exactly what I want, but where you have "INNER JOIN Job001 J" I need this to be dynamic based on the value retrieved in "JR.JobNumber". This probably isn't even possible, but I thought I'd ask. so something like:
– squatman
Nov 24 '18 at 17:48
Please see response as an answer.
– squatman
Nov 24 '18 at 17:59
@squatman That's a very bad idea, you will need to use DynamicSQL
– Sami
Nov 24 '18 at 18:10
Ok Sami, thanks for your help. I guess for the time being I will have to get the result with some programming in the application. Thanks.
– squatman
Nov 24 '18 at 18:15
@squatman How would you know which Job table to join with? Job001? Job002?...etc
– Sami
Nov 24 '18 at 18:17
|
show 3 more comments
Sami, thank you very much for taking the time to do this. This is exactly what I want, but where you have "INNER JOIN Job001 J" I need this to be dynamic based on the value retrieved in "JR.JobNumber". This probably isn't even possible, but I thought I'd ask. so something like:
– squatman
Nov 24 '18 at 17:48
Please see response as an answer.
– squatman
Nov 24 '18 at 17:59
@squatman That's a very bad idea, you will need to use DynamicSQL
– Sami
Nov 24 '18 at 18:10
Ok Sami, thanks for your help. I guess for the time being I will have to get the result with some programming in the application. Thanks.
– squatman
Nov 24 '18 at 18:15
@squatman How would you know which Job table to join with? Job001? Job002?...etc
– Sami
Nov 24 '18 at 18:17
Sami, thank you very much for taking the time to do this. This is exactly what I want, but where you have "INNER JOIN Job001 J" I need this to be dynamic based on the value retrieved in "JR.JobNumber". This probably isn't even possible, but I thought I'd ask. so something like:
– squatman
Nov 24 '18 at 17:48
Sami, thank you very much for taking the time to do this. This is exactly what I want, but where you have "INNER JOIN Job001 J" I need this to be dynamic based on the value retrieved in "JR.JobNumber". This probably isn't even possible, but I thought I'd ask. so something like:
– squatman
Nov 24 '18 at 17:48
Please see response as an answer.
– squatman
Nov 24 '18 at 17:59
Please see response as an answer.
– squatman
Nov 24 '18 at 17:59
@squatman That's a very bad idea, you will need to use DynamicSQL
– Sami
Nov 24 '18 at 18:10
@squatman That's a very bad idea, you will need to use DynamicSQL
– Sami
Nov 24 '18 at 18:10
Ok Sami, thanks for your help. I guess for the time being I will have to get the result with some programming in the application. Thanks.
– squatman
Nov 24 '18 at 18:15
Ok Sami, thanks for your help. I guess for the time being I will have to get the result with some programming in the application. Thanks.
– squatman
Nov 24 '18 at 18:15
@squatman How would you know which Job table to join with? Job001? Job002?...etc
– Sami
Nov 24 '18 at 18:17
@squatman How would you know which Job table to join with? Job001? Job002?...etc
– Sami
Nov 24 '18 at 18:17
|
show 3 more comments
-1
SELECT JR.JobNumber,
JR.IsComplete,
J.DateModified
FROM JobRegister JR INNER JOIN CONCAT('Job', JR.JobNumber) J
ON JR.JobNumber = J.JobNumber
WHERE DocumentRevision = 'B';
answered Nov 24 '18 at 17:52
squatmansquatman
225
add a comment |
-1
SELECT JR.JobNumber,
JR.IsComplete,
J.DateModified
FROM JobRegister JR INNER JOIN CONCAT('Job', JR.JobNumber) J
ON JR.JobNumber = J.JobNumber
WHERE DocumentRevision = 'B';
answered Nov 24 '18 at 17:52
squatmansquatman
225
add a comment |
-1
-1
-1
SELECT JR.JobNumber,
JR.IsComplete,
J.DateModified
FROM JobRegister JR INNER JOIN CONCAT('Job', JR.JobNumber) J
ON JR.JobNumber = J.JobNumber
WHERE DocumentRevision = 'B';
answered Nov 24 '18 at 17:52
squatmansquatman
225
SELECT JR.JobNumber,
JR.IsComplete,
J.DateModified
FROM JobRegister JR INNER JOIN CONCAT('Job', JR.JobNumber) J
ON JR.JobNumber = J.JobNumber
WHERE DocumentRevision = 'B';
answered Nov 24 '18 at 17:52
squatmansquatman
225
answered Nov 24 '18 at 17:52
squatmansquatman
225
answered Nov 24 '18 at 17:52
squatmansquatman
225
answered Nov 24 '18 at 17:52
squatmansquatman
225
225
add a comment |
add a comment |
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%2f53459803%2fusing-query-result-field-as-database-name-in-same-query%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
3
It is not clear to me how we can even join these two tables together. You might want to explain that better.
– Tim Biegeleisen
Nov 24 '18 at 15:52
1
Database <> Table. Those are tables, not databases. Tables are just one type of object that a database can contain.
– Larnu
Nov 24 '18 at 15:55
1
I guess by JobNumber, but why in the first table the datatype is varchar and for the second is INT?
– Sami
Nov 24 '18 at 15:56
So, you have a
Job002andJob003, etc table? If so, this screams bad design. The better solution here is to fix your design. Is this something that you are able to do?– Larnu
Nov 24 '18 at 15:57
Also, why is the value for
date_modifiedin your Query results05/11/2018? The latest date in yourJob001table is10/11/2018; should the value not therefore be that?– Larnu
Nov 24 '18 at 16:05