Trouble joining the tables
-1
I am trying to join two tables but there is a problem with it.
No error pops up but the columns of the table after joining are not showing up.
This is the query I have made in order to find the date difference and then join the table with another one.
SELECT * FROM
(Select job_empl_job.*,(job_empl_job.emplj_lastdate -trunc(sysdate))
AS
difference from job_empl_job
INNER JOIN
job_city
ON
job_city.city_id=job_empl_job.emplj_city)
WHERE difference < 16 AND difference > 0
sql oracle oracle11g
edited Nov 22 '18 at 5:38
Hamza Haider
654315
asked Nov 22 '18 at 5:36
Ali TahirAli Tahir
186
add a comment |
-1
I am trying to join two tables but there is a problem with it.
No error pops up but the columns of the table after joining are not showing up.
This is the query I have made in order to find the date difference and then join the table with another one.
SELECT * FROM
(Select job_empl_job.*,(job_empl_job.emplj_lastdate -trunc(sysdate))
AS
difference from job_empl_job
INNER JOIN
job_city
ON
job_city.city_id=job_empl_job.emplj_city)
WHERE difference < 16 AND difference > 0
sql oracle oracle11g
edited Nov 22 '18 at 5:38
Hamza Haider
654315
asked Nov 22 '18 at 5:36
Ali TahirAli Tahir
186
Just a note of explanation as to why you're not seeing the columns you're expecting to see... it's because you haven't added them into the select list. Do that, and you should be fine (e.g.select job_empl_job.*, job_city.*, ...)
– Boneist
Nov 22 '18 at 9:59
add a comment |
-1
-1
-1
1
I am trying to join two tables but there is a problem with it.
No error pops up but the columns of the table after joining are not showing up.
This is the query I have made in order to find the date difference and then join the table with another one.
SELECT * FROM
(Select job_empl_job.*,(job_empl_job.emplj_lastdate -trunc(sysdate))
AS
difference from job_empl_job
INNER JOIN
job_city
ON
job_city.city_id=job_empl_job.emplj_city)
WHERE difference < 16 AND difference > 0
sql oracle oracle11g
edited Nov 22 '18 at 5:38
Hamza Haider
654315
asked Nov 22 '18 at 5:36
Ali TahirAli Tahir
186
I am trying to join two tables but there is a problem with it.
No error pops up but the columns of the table after joining are not showing up.
This is the query I have made in order to find the date difference and then join the table with another one.
SELECT * FROM
(Select job_empl_job.*,(job_empl_job.emplj_lastdate -trunc(sysdate))
AS
difference from job_empl_job
INNER JOIN
job_city
ON
job_city.city_id=job_empl_job.emplj_city)
WHERE difference < 16 AND difference > 0
sql oracle oracle11g
sql oracle oracle11g
edited Nov 22 '18 at 5:38
Hamza Haider
654315
asked Nov 22 '18 at 5:36
Ali TahirAli Tahir
186
edited Nov 22 '18 at 5:38
Hamza Haider
654315
asked Nov 22 '18 at 5:36
Ali TahirAli Tahir
186
edited Nov 22 '18 at 5:38
Hamza Haider
654315
edited Nov 22 '18 at 5:38
Hamza Haider
654315
edited Nov 22 '18 at 5:38
Hamza Haider
654315
654315
asked Nov 22 '18 at 5:36
Ali TahirAli Tahir
186
asked Nov 22 '18 at 5:36
Ali TahirAli Tahir
186
asked Nov 22 '18 at 5:36
Ali TahirAli Tahir
186
186
Just a note of explanation as to why you're not seeing the columns you're expecting to see... it's because you haven't added them into the select list. Do that, and you should be fine (e.g.select job_empl_job.*, job_city.*, ...)
– Boneist
Nov 22 '18 at 9:59
add a comment |
Just a note of explanation as to why you're not seeing the columns you're expecting to see... it's because you haven't added them into the select list. Do that, and you should be fine (e.g.select job_empl_job.*, job_city.*, ...)
– Boneist
Nov 22 '18 at 9:59
Just a note of explanation as to why you're not seeing the columns you're expecting to see... it's because you haven't added them into the select list. Do that, and you should be fine (e.g.
select job_empl_job.*, job_city.*, ...)– Boneist
Nov 22 '18 at 9:59
Just a note of explanation as to why you're not seeing the columns you're expecting to see... it's because you haven't added them into the select list. Do that, and you should be fine (e.g.
select job_empl_job.*, job_city.*, ...)– Boneist
Nov 22 '18 at 9:59
add a comment |
2 Answers
2
active
oldest
votes
1
Try this one:
SELECT * FROM
(
SELECT a.* , b.* ,
(SELECT trunc(EMPLJ_LASTDATE - sysdate ) from dual )as days
FROM
job_empl_job a
INNER JOIN
JOB_CITY b
ON
a.EMPLJ_CITY=b.CITY_ID
)
WHERE
days < 16 and days > 0
answered Nov 22 '18 at 5:56
Jahanzeb AwanJahanzeb Awan
6310
I highly appreciate fa06 for his help but I have approved the solution that is working for me and not based on anyone's opinion.
– Ali Tahir
Nov 22 '18 at 6:38
@JahanzebAwan You don't need the scalar subquery to do the date difference; I'm not sure why you have added it in?
– Boneist
Nov 22 '18 at 9:58
add a comment |
3
You can try below
Select job_empl_job.*,trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy') AS difference
from job_empl_job INNER JOIN job_city
ON
job_city.city_id=job_empl_job.emplj_city
WHERE trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy')> 0 and trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy') < 16
answered Nov 22 '18 at 5:39
fa06fa06
11.7k2917
Data of job_city table is still not showing up
– Ali Tahir
Nov 22 '18 at 5:42
This join should be correct job_city.city_id=job_empl_job.emplj_city is it correct? is is on the primary key and foreign key?
– Hamza Haider
Nov 22 '18 at 5:44
@AliTahir, updated my answer - you can check now
– fa06
Nov 22 '18 at 5:47
Now data of first table is also not showing up
– Ali Tahir
Nov 22 '18 at 5:51
1
@fa06 thank you for your help. I have found the solution but I highly appreciate your effort.
– Ali Tahir
Nov 22 '18 at 6:42
|
show 1 more 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%2f53424478%2ftrouble-joining-the-tables%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
1
Try this one:
SELECT * FROM
(
SELECT a.* , b.* ,
(SELECT trunc(EMPLJ_LASTDATE - sysdate ) from dual )as days
FROM
job_empl_job a
INNER JOIN
JOB_CITY b
ON
a.EMPLJ_CITY=b.CITY_ID
)
WHERE
days < 16 and days > 0
answered Nov 22 '18 at 5:56
Jahanzeb AwanJahanzeb Awan
6310
I highly appreciate fa06 for his help but I have approved the solution that is working for me and not based on anyone's opinion.
– Ali Tahir
Nov 22 '18 at 6:38
@JahanzebAwan You don't need the scalar subquery to do the date difference; I'm not sure why you have added it in?
– Boneist
Nov 22 '18 at 9:58
add a comment |
1
Try this one:
SELECT * FROM
(
SELECT a.* , b.* ,
(SELECT trunc(EMPLJ_LASTDATE - sysdate ) from dual )as days
FROM
job_empl_job a
INNER JOIN
JOB_CITY b
ON
a.EMPLJ_CITY=b.CITY_ID
)
WHERE
days < 16 and days > 0
answered Nov 22 '18 at 5:56
Jahanzeb AwanJahanzeb Awan
6310
I highly appreciate fa06 for his help but I have approved the solution that is working for me and not based on anyone's opinion.
– Ali Tahir
Nov 22 '18 at 6:38
@JahanzebAwan You don't need the scalar subquery to do the date difference; I'm not sure why you have added it in?
– Boneist
Nov 22 '18 at 9:58
add a comment |
1
1
1
Try this one:
SELECT * FROM
(
SELECT a.* , b.* ,
(SELECT trunc(EMPLJ_LASTDATE - sysdate ) from dual )as days
FROM
job_empl_job a
INNER JOIN
JOB_CITY b
ON
a.EMPLJ_CITY=b.CITY_ID
)
WHERE
days < 16 and days > 0
answered Nov 22 '18 at 5:56
Jahanzeb AwanJahanzeb Awan
6310
Try this one:
SELECT * FROM
(
SELECT a.* , b.* ,
(SELECT trunc(EMPLJ_LASTDATE - sysdate ) from dual )as days
FROM
job_empl_job a
INNER JOIN
JOB_CITY b
ON
a.EMPLJ_CITY=b.CITY_ID
)
WHERE
days < 16 and days > 0
answered Nov 22 '18 at 5:56
Jahanzeb AwanJahanzeb Awan
6310
answered Nov 22 '18 at 5:56
Jahanzeb AwanJahanzeb Awan
6310
answered Nov 22 '18 at 5:56
Jahanzeb AwanJahanzeb Awan
6310
answered Nov 22 '18 at 5:56
Jahanzeb AwanJahanzeb Awan
6310
6310
I highly appreciate fa06 for his help but I have approved the solution that is working for me and not based on anyone's opinion.
– Ali Tahir
Nov 22 '18 at 6:38
@JahanzebAwan You don't need the scalar subquery to do the date difference; I'm not sure why you have added it in?
– Boneist
Nov 22 '18 at 9:58
add a comment |
I highly appreciate fa06 for his help but I have approved the solution that is working for me and not based on anyone's opinion.
– Ali Tahir
Nov 22 '18 at 6:38
@JahanzebAwan You don't need the scalar subquery to do the date difference; I'm not sure why you have added it in?
– Boneist
Nov 22 '18 at 9:58
I highly appreciate fa06 for his help but I have approved the solution that is working for me and not based on anyone's opinion.
– Ali Tahir
Nov 22 '18 at 6:38
I highly appreciate fa06 for his help but I have approved the solution that is working for me and not based on anyone's opinion.
– Ali Tahir
Nov 22 '18 at 6:38
@JahanzebAwan You don't need the scalar subquery to do the date difference; I'm not sure why you have added it in?
– Boneist
Nov 22 '18 at 9:58
@JahanzebAwan You don't need the scalar subquery to do the date difference; I'm not sure why you have added it in?
– Boneist
Nov 22 '18 at 9:58
add a comment |
3
You can try below
Select job_empl_job.*,trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy') AS difference
from job_empl_job INNER JOIN job_city
ON
job_city.city_id=job_empl_job.emplj_city
WHERE trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy')> 0 and trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy') < 16
answered Nov 22 '18 at 5:39
fa06fa06
11.7k2917
Data of job_city table is still not showing up
– Ali Tahir
Nov 22 '18 at 5:42
This join should be correct job_city.city_id=job_empl_job.emplj_city is it correct? is is on the primary key and foreign key?
– Hamza Haider
Nov 22 '18 at 5:44
@AliTahir, updated my answer - you can check now
– fa06
Nov 22 '18 at 5:47
Now data of first table is also not showing up
– Ali Tahir
Nov 22 '18 at 5:51
1
@fa06 thank you for your help. I have found the solution but I highly appreciate your effort.
– Ali Tahir
Nov 22 '18 at 6:42
|
show 1 more comment
3
You can try below
Select job_empl_job.*,trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy') AS difference
from job_empl_job INNER JOIN job_city
ON
job_city.city_id=job_empl_job.emplj_city
WHERE trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy')> 0 and trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy') < 16
answered Nov 22 '18 at 5:39
fa06fa06
11.7k2917
Data of job_city table is still not showing up
– Ali Tahir
Nov 22 '18 at 5:42
This join should be correct job_city.city_id=job_empl_job.emplj_city is it correct? is is on the primary key and foreign key?
– Hamza Haider
Nov 22 '18 at 5:44
@AliTahir, updated my answer - you can check now
– fa06
Nov 22 '18 at 5:47
Now data of first table is also not showing up
– Ali Tahir
Nov 22 '18 at 5:51
1
@fa06 thank you for your help. I have found the solution but I highly appreciate your effort.
– Ali Tahir
Nov 22 '18 at 6:42
|
show 1 more comment
3
3
3
You can try below
Select job_empl_job.*,trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy') AS difference
from job_empl_job INNER JOIN job_city
ON
job_city.city_id=job_empl_job.emplj_city
WHERE trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy')> 0 and trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy') < 16
answered Nov 22 '18 at 5:39
fa06fa06
11.7k2917
You can try below
Select job_empl_job.*,trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy') AS difference
from job_empl_job INNER JOIN job_city
ON
job_city.city_id=job_empl_job.emplj_city
WHERE trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy')> 0 and trunc(sysdate)-TO_date(job_empl_job.emplj_lastdate, 'dd/mm/yyyy') < 16
answered Nov 22 '18 at 5:39
fa06fa06
11.7k2917
edited Nov 22 '18 at 5:46
answered Nov 22 '18 at 5:39
fa06fa06
11.7k2917
answered Nov 22 '18 at 5:39
fa06fa06
11.7k2917
answered Nov 22 '18 at 5:39
fa06fa06
11.7k2917
11.7k2917
Data of job_city table is still not showing up
– Ali Tahir
Nov 22 '18 at 5:42
This join should be correct job_city.city_id=job_empl_job.emplj_city is it correct? is is on the primary key and foreign key?
– Hamza Haider
Nov 22 '18 at 5:44
@AliTahir, updated my answer - you can check now
– fa06
Nov 22 '18 at 5:47
Now data of first table is also not showing up
– Ali Tahir
Nov 22 '18 at 5:51
1
@fa06 thank you for your help. I have found the solution but I highly appreciate your effort.
– Ali Tahir
Nov 22 '18 at 6:42
|
show 1 more comment
Data of job_city table is still not showing up
– Ali Tahir
Nov 22 '18 at 5:42
This join should be correct job_city.city_id=job_empl_job.emplj_city is it correct? is is on the primary key and foreign key?
– Hamza Haider
Nov 22 '18 at 5:44
@AliTahir, updated my answer - you can check now
– fa06
Nov 22 '18 at 5:47
Now data of first table is also not showing up
– Ali Tahir
Nov 22 '18 at 5:51
1
@fa06 thank you for your help. I have found the solution but I highly appreciate your effort.
– Ali Tahir
Nov 22 '18 at 6:42
Data of job_city table is still not showing up
– Ali Tahir
Nov 22 '18 at 5:42
Data of job_city table is still not showing up
– Ali Tahir
Nov 22 '18 at 5:42
This join should be correct job_city.city_id=job_empl_job.emplj_city is it correct? is is on the primary key and foreign key?
– Hamza Haider
Nov 22 '18 at 5:44
This join should be correct job_city.city_id=job_empl_job.emplj_city is it correct? is is on the primary key and foreign key?
– Hamza Haider
Nov 22 '18 at 5:44
@AliTahir, updated my answer - you can check now
– fa06
Nov 22 '18 at 5:47
@AliTahir, updated my answer - you can check now
– fa06
Nov 22 '18 at 5:47
Now data of first table is also not showing up
– Ali Tahir
Nov 22 '18 at 5:51
Now data of first table is also not showing up
– Ali Tahir
Nov 22 '18 at 5:51
1
1
@fa06 thank you for your help. I have found the solution but I highly appreciate your effort.
– Ali Tahir
Nov 22 '18 at 6:42
@fa06 thank you for your help. I have found the solution but I highly appreciate your effort.
– Ali Tahir
Nov 22 '18 at 6:42
|
show 1 more 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%2f53424478%2ftrouble-joining-the-tables%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
Just a note of explanation as to why you're not seeing the columns you're expecting to see... it's because you haven't added them into the select list. Do that, and you should be fine (e.g.
select job_empl_job.*, job_city.*, ...)– Boneist
Nov 22 '18 at 9:59