Left JOIN query not displaying data
-2
SELECT Name,Checktime
FROM Userinfo
LEFT JOIN Checkinout ON USERINFO.USERID = CHECKINOUT.USERID
WHERE Checktime >= #11/14/18 04:35:00 AM#;
The above query has to display all name from userinfo table but it only displays matching data with checkinout table.
enter image description here
php sql ms-access
asked Nov 15 '18 at 7:19
farith firdousfarith firdous
126
|
show 7 more comments
-2
SELECT Name,Checktime
FROM Userinfo
LEFT JOIN Checkinout ON USERINFO.USERID = CHECKINOUT.USERID
WHERE Checktime >= #11/14/18 04:35:00 AM#;
The above query has to display all name from userinfo table but it only displays matching data with checkinout table.
enter image description here
php sql ms-access
asked Nov 15 '18 at 7:19
farith firdousfarith firdous
126
1
ofcourse it will show from checkinout table because you are usingRIGHT JOIN
– line88
Nov 15 '18 at 7:23
Even, i changed to left JOIN still am getting same data
– farith firdous
Nov 15 '18 at 7:25
Which dbms are you using?
– jarlh
Nov 15 '18 at 7:35
Add some sample table data and the expected result - all as formatted text, not images (or links to images...)
– jarlh
Nov 15 '18 at 7:36
Ms access, i have added an image
– farith firdous
Nov 15 '18 at 7:39
|
show 7 more comments
-2
-2
-2
SELECT Name,Checktime
FROM Userinfo
LEFT JOIN Checkinout ON USERINFO.USERID = CHECKINOUT.USERID
WHERE Checktime >= #11/14/18 04:35:00 AM#;
The above query has to display all name from userinfo table but it only displays matching data with checkinout table.
enter image description here
php sql ms-access
asked Nov 15 '18 at 7:19
farith firdousfarith firdous
126
SELECT Name,Checktime
FROM Userinfo
LEFT JOIN Checkinout ON USERINFO.USERID = CHECKINOUT.USERID
WHERE Checktime >= #11/14/18 04:35:00 AM#;
The above query has to display all name from userinfo table but it only displays matching data with checkinout table.
enter image description here
php sql ms-access
php sql ms-access
asked Nov 15 '18 at 7:19
farith firdousfarith firdous
126
asked Nov 15 '18 at 7:19
farith firdousfarith firdous
126
edited Nov 15 '18 at 8:59
farith firdous
asked Nov 15 '18 at 7:19
farith firdousfarith firdous
126
asked Nov 15 '18 at 7:19
farith firdousfarith firdous
126
asked Nov 15 '18 at 7:19
farith firdousfarith firdous
126
126
1
ofcourse it will show from checkinout table because you are usingRIGHT JOIN
– line88
Nov 15 '18 at 7:23
Even, i changed to left JOIN still am getting same data
– farith firdous
Nov 15 '18 at 7:25
Which dbms are you using?
– jarlh
Nov 15 '18 at 7:35
Add some sample table data and the expected result - all as formatted text, not images (or links to images...)
– jarlh
Nov 15 '18 at 7:36
Ms access, i have added an image
– farith firdous
Nov 15 '18 at 7:39
|
show 7 more comments
1
ofcourse it will show from checkinout table because you are usingRIGHT JOIN
– line88
Nov 15 '18 at 7:23
Even, i changed to left JOIN still am getting same data
– farith firdous
Nov 15 '18 at 7:25
Which dbms are you using?
– jarlh
Nov 15 '18 at 7:35
Add some sample table data and the expected result - all as formatted text, not images (or links to images...)
– jarlh
Nov 15 '18 at 7:36
Ms access, i have added an image
– farith firdous
Nov 15 '18 at 7:39
1
1
ofcourse it will show from checkinout table because you are using
RIGHT JOIN– line88
Nov 15 '18 at 7:23
ofcourse it will show from checkinout table because you are using
RIGHT JOIN– line88
Nov 15 '18 at 7:23
Even, i changed to left JOIN still am getting same data
– farith firdous
Nov 15 '18 at 7:25
Even, i changed to left JOIN still am getting same data
– farith firdous
Nov 15 '18 at 7:25
Which dbms are you using?
– jarlh
Nov 15 '18 at 7:35
Which dbms are you using?
– jarlh
Nov 15 '18 at 7:35
Add some sample table data and the expected result - all as formatted text, not images (or links to images...)
– jarlh
Nov 15 '18 at 7:36
Add some sample table data and the expected result - all as formatted text, not images (or links to images...)
– jarlh
Nov 15 '18 at 7:36
Ms access, i have added an image
– farith firdous
Nov 15 '18 at 7:39
Ms access, i have added an image
– farith firdous
Nov 15 '18 at 7:39
|
show 7 more comments
4 Answers
4
active
oldest
votes
0
In most databases, you would move the condition to the ON clause:
SELECT ui.Name, cio.Checktime
FROM Userinfo as ui LEFT JOIN
Checkinout as cio
ON ui.USERID = cio.USERID AND
cio.Checktime >= #11/14/18 04:35:00 AM#;
Alas, this standard syntax is not supported in MS Access. You can use a subquery instead:
SELECT ui.Name, cio.Checktime
FROM Userinfo as ui LEFT JOIN
(SELECT cio.*
FROM Checkinout as cio
WHERE cio.Checktime >= #11/14/18 04:35:00 AM#
) as cio
ON ui.USERID = cio.USERID ;
answered Nov 15 '18 at 12:40
Gordon LinoffGordon Linoff
766k35297401
My god, it works, Thank you so much !!!
– farith firdous
Nov 16 '18 at 18:09
add a comment |
0
Your question title says LEFT JOIN not working but in your query, you are using RIGHT JOIN?
Just use LEFT JOIN in your query. It will list all users from USERINFO but only matching records from CHECKINOUT.
SELECT `Name`, `Checktime`
FROM `Userinfo`
LEFT JOIN `Checkinout` ON `USERINFO`.`USERID` = `CHECKINOUT`.`USERID`
Where `Checktime` >= #11/14/18 04:35:00 AM#;
answered Nov 15 '18 at 7:25
d.coderd.coder
1,4051222
No, still same data
– farith firdous
Nov 15 '18 at 7:29
Add sample data from both tables in your question then I will be able to comment further.
– d.coder
Nov 15 '18 at 7:30
Please check the image
– farith firdous
Nov 15 '18 at 7:38
add a comment |
0
You can set alias on each table
SELECT a.*,b.*
FROM Userinfo a
LEFT JOIN Checkinout b ON a.USERID = b.USERID
Where b.Checktime>=#11/14/18 04:35:00 AM#;
answered Nov 15 '18 at 7:43
JL BarcelonaJL Barcelona
334
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
add a comment |
0
If you want to display all name from the userinfo and display the match data from the checkinout table you just need to change RIGHT JOIN to LEFT JOIN.
SELECT Checkinout.USERID, Userinfo.Name, Userinfo.Checktime,
FROM Userinfo
LEFT JOIN Checkinout ON USERINFO.USERID = CHECKINOUT.USERID
WHERE Checktime >= #11/14/18;
Try this one.
answered Nov 15 '18 at 7:27
UnknownUnknown
639
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
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%2f53314239%2fleft-join-query-not-displaying-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
In most databases, you would move the condition to the ON clause:
SELECT ui.Name, cio.Checktime
FROM Userinfo as ui LEFT JOIN
Checkinout as cio
ON ui.USERID = cio.USERID AND
cio.Checktime >= #11/14/18 04:35:00 AM#;
Alas, this standard syntax is not supported in MS Access. You can use a subquery instead:
SELECT ui.Name, cio.Checktime
FROM Userinfo as ui LEFT JOIN
(SELECT cio.*
FROM Checkinout as cio
WHERE cio.Checktime >= #11/14/18 04:35:00 AM#
) as cio
ON ui.USERID = cio.USERID ;
answered Nov 15 '18 at 12:40
Gordon LinoffGordon Linoff
766k35297401
My god, it works, Thank you so much !!!
– farith firdous
Nov 16 '18 at 18:09
add a comment |
0
In most databases, you would move the condition to the ON clause:
SELECT ui.Name, cio.Checktime
FROM Userinfo as ui LEFT JOIN
Checkinout as cio
ON ui.USERID = cio.USERID AND
cio.Checktime >= #11/14/18 04:35:00 AM#;
Alas, this standard syntax is not supported in MS Access. You can use a subquery instead:
SELECT ui.Name, cio.Checktime
FROM Userinfo as ui LEFT JOIN
(SELECT cio.*
FROM Checkinout as cio
WHERE cio.Checktime >= #11/14/18 04:35:00 AM#
) as cio
ON ui.USERID = cio.USERID ;
answered Nov 15 '18 at 12:40
Gordon LinoffGordon Linoff
766k35297401
My god, it works, Thank you so much !!!
– farith firdous
Nov 16 '18 at 18:09
add a comment |
0
0
0
In most databases, you would move the condition to the ON clause:
SELECT ui.Name, cio.Checktime
FROM Userinfo as ui LEFT JOIN
Checkinout as cio
ON ui.USERID = cio.USERID AND
cio.Checktime >= #11/14/18 04:35:00 AM#;
Alas, this standard syntax is not supported in MS Access. You can use a subquery instead:
SELECT ui.Name, cio.Checktime
FROM Userinfo as ui LEFT JOIN
(SELECT cio.*
FROM Checkinout as cio
WHERE cio.Checktime >= #11/14/18 04:35:00 AM#
) as cio
ON ui.USERID = cio.USERID ;
answered Nov 15 '18 at 12:40
Gordon LinoffGordon Linoff
766k35297401
In most databases, you would move the condition to the ON clause:
SELECT ui.Name, cio.Checktime
FROM Userinfo as ui LEFT JOIN
Checkinout as cio
ON ui.USERID = cio.USERID AND
cio.Checktime >= #11/14/18 04:35:00 AM#;
Alas, this standard syntax is not supported in MS Access. You can use a subquery instead:
SELECT ui.Name, cio.Checktime
FROM Userinfo as ui LEFT JOIN
(SELECT cio.*
FROM Checkinout as cio
WHERE cio.Checktime >= #11/14/18 04:35:00 AM#
) as cio
ON ui.USERID = cio.USERID ;
answered Nov 15 '18 at 12:40
Gordon LinoffGordon Linoff
766k35297401
answered Nov 15 '18 at 12:40
Gordon LinoffGordon Linoff
766k35297401
answered Nov 15 '18 at 12:40
Gordon LinoffGordon Linoff
766k35297401
answered Nov 15 '18 at 12:40
Gordon LinoffGordon Linoff
766k35297401
766k35297401
My god, it works, Thank you so much !!!
– farith firdous
Nov 16 '18 at 18:09
add a comment |
My god, it works, Thank you so much !!!
– farith firdous
Nov 16 '18 at 18:09
My god, it works, Thank you so much !!!
– farith firdous
Nov 16 '18 at 18:09
My god, it works, Thank you so much !!!
– farith firdous
Nov 16 '18 at 18:09
add a comment |
0
Your question title says LEFT JOIN not working but in your query, you are using RIGHT JOIN?
Just use LEFT JOIN in your query. It will list all users from USERINFO but only matching records from CHECKINOUT.
SELECT `Name`, `Checktime`
FROM `Userinfo`
LEFT JOIN `Checkinout` ON `USERINFO`.`USERID` = `CHECKINOUT`.`USERID`
Where `Checktime` >= #11/14/18 04:35:00 AM#;
answered Nov 15 '18 at 7:25
d.coderd.coder
1,4051222
No, still same data
– farith firdous
Nov 15 '18 at 7:29
Add sample data from both tables in your question then I will be able to comment further.
– d.coder
Nov 15 '18 at 7:30
Please check the image
– farith firdous
Nov 15 '18 at 7:38
add a comment |
0
Your question title says LEFT JOIN not working but in your query, you are using RIGHT JOIN?
Just use LEFT JOIN in your query. It will list all users from USERINFO but only matching records from CHECKINOUT.
SELECT `Name`, `Checktime`
FROM `Userinfo`
LEFT JOIN `Checkinout` ON `USERINFO`.`USERID` = `CHECKINOUT`.`USERID`
Where `Checktime` >= #11/14/18 04:35:00 AM#;
answered Nov 15 '18 at 7:25
d.coderd.coder
1,4051222
No, still same data
– farith firdous
Nov 15 '18 at 7:29
Add sample data from both tables in your question then I will be able to comment further.
– d.coder
Nov 15 '18 at 7:30
Please check the image
– farith firdous
Nov 15 '18 at 7:38
add a comment |
0
0
0
Your question title says LEFT JOIN not working but in your query, you are using RIGHT JOIN?
Just use LEFT JOIN in your query. It will list all users from USERINFO but only matching records from CHECKINOUT.
SELECT `Name`, `Checktime`
FROM `Userinfo`
LEFT JOIN `Checkinout` ON `USERINFO`.`USERID` = `CHECKINOUT`.`USERID`
Where `Checktime` >= #11/14/18 04:35:00 AM#;
answered Nov 15 '18 at 7:25
d.coderd.coder
1,4051222
Your question title says LEFT JOIN not working but in your query, you are using RIGHT JOIN?
Just use LEFT JOIN in your query. It will list all users from USERINFO but only matching records from CHECKINOUT.
SELECT `Name`, `Checktime`
FROM `Userinfo`
LEFT JOIN `Checkinout` ON `USERINFO`.`USERID` = `CHECKINOUT`.`USERID`
Where `Checktime` >= #11/14/18 04:35:00 AM#;
answered Nov 15 '18 at 7:25
d.coderd.coder
1,4051222
answered Nov 15 '18 at 7:25
d.coderd.coder
1,4051222
answered Nov 15 '18 at 7:25
d.coderd.coder
1,4051222
answered Nov 15 '18 at 7:25
d.coderd.coder
1,4051222
1,4051222
No, still same data
– farith firdous
Nov 15 '18 at 7:29
Add sample data from both tables in your question then I will be able to comment further.
– d.coder
Nov 15 '18 at 7:30
Please check the image
– farith firdous
Nov 15 '18 at 7:38
add a comment |
No, still same data
– farith firdous
Nov 15 '18 at 7:29
Add sample data from both tables in your question then I will be able to comment further.
– d.coder
Nov 15 '18 at 7:30
Please check the image
– farith firdous
Nov 15 '18 at 7:38
No, still same data
– farith firdous
Nov 15 '18 at 7:29
No, still same data
– farith firdous
Nov 15 '18 at 7:29
Add sample data from both tables in your question then I will be able to comment further.
– d.coder
Nov 15 '18 at 7:30
Add sample data from both tables in your question then I will be able to comment further.
– d.coder
Nov 15 '18 at 7:30
Please check the image
– farith firdous
Nov 15 '18 at 7:38
Please check the image
– farith firdous
Nov 15 '18 at 7:38
add a comment |
0
You can set alias on each table
SELECT a.*,b.*
FROM Userinfo a
LEFT JOIN Checkinout b ON a.USERID = b.USERID
Where b.Checktime>=#11/14/18 04:35:00 AM#;
answered Nov 15 '18 at 7:43
JL BarcelonaJL Barcelona
334
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
add a comment |
0
You can set alias on each table
SELECT a.*,b.*
FROM Userinfo a
LEFT JOIN Checkinout b ON a.USERID = b.USERID
Where b.Checktime>=#11/14/18 04:35:00 AM#;
answered Nov 15 '18 at 7:43
JL BarcelonaJL Barcelona
334
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
add a comment |
0
0
0
You can set alias on each table
SELECT a.*,b.*
FROM Userinfo a
LEFT JOIN Checkinout b ON a.USERID = b.USERID
Where b.Checktime>=#11/14/18 04:35:00 AM#;
answered Nov 15 '18 at 7:43
JL BarcelonaJL Barcelona
334
You can set alias on each table
SELECT a.*,b.*
FROM Userinfo a
LEFT JOIN Checkinout b ON a.USERID = b.USERID
Where b.Checktime>=#11/14/18 04:35:00 AM#;
answered Nov 15 '18 at 7:43
JL BarcelonaJL Barcelona
334
edited Nov 15 '18 at 7:48
answered Nov 15 '18 at 7:43
JL BarcelonaJL Barcelona
334
answered Nov 15 '18 at 7:43
JL BarcelonaJL Barcelona
334
answered Nov 15 '18 at 7:43
JL BarcelonaJL Barcelona
334
334
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
add a comment |
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
add a comment |
0
If you want to display all name from the userinfo and display the match data from the checkinout table you just need to change RIGHT JOIN to LEFT JOIN.
SELECT Checkinout.USERID, Userinfo.Name, Userinfo.Checktime,
FROM Userinfo
LEFT JOIN Checkinout ON USERINFO.USERID = CHECKINOUT.USERID
WHERE Checktime >= #11/14/18;
Try this one.
answered Nov 15 '18 at 7:27
UnknownUnknown
639
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
add a comment |
0
If you want to display all name from the userinfo and display the match data from the checkinout table you just need to change RIGHT JOIN to LEFT JOIN.
SELECT Checkinout.USERID, Userinfo.Name, Userinfo.Checktime,
FROM Userinfo
LEFT JOIN Checkinout ON USERINFO.USERID = CHECKINOUT.USERID
WHERE Checktime >= #11/14/18;
Try this one.
answered Nov 15 '18 at 7:27
UnknownUnknown
639
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
add a comment |
0
0
0
If you want to display all name from the userinfo and display the match data from the checkinout table you just need to change RIGHT JOIN to LEFT JOIN.
SELECT Checkinout.USERID, Userinfo.Name, Userinfo.Checktime,
FROM Userinfo
LEFT JOIN Checkinout ON USERINFO.USERID = CHECKINOUT.USERID
WHERE Checktime >= #11/14/18;
Try this one.
answered Nov 15 '18 at 7:27
UnknownUnknown
639
If you want to display all name from the userinfo and display the match data from the checkinout table you just need to change RIGHT JOIN to LEFT JOIN.
SELECT Checkinout.USERID, Userinfo.Name, Userinfo.Checktime,
FROM Userinfo
LEFT JOIN Checkinout ON USERINFO.USERID = CHECKINOUT.USERID
WHERE Checktime >= #11/14/18;
Try this one.
answered Nov 15 '18 at 7:27
UnknownUnknown
639
edited Nov 15 '18 at 9:46
answered Nov 15 '18 at 7:27
UnknownUnknown
639
answered Nov 15 '18 at 7:27
UnknownUnknown
639
answered Nov 15 '18 at 7:27
UnknownUnknown
639
639
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
add a comment |
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
Comments are not for extended discussion; this conversation has been moved to chat.
– Samuel Liew♦
Nov 15 '18 at 12:27
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%2f53314239%2fleft-join-query-not-displaying-data%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
ofcourse it will show from checkinout table because you are using
RIGHT JOIN– line88
Nov 15 '18 at 7:23
Even, i changed to left JOIN still am getting same data
– farith firdous
Nov 15 '18 at 7:25
Which dbms are you using?
– jarlh
Nov 15 '18 at 7:35
Add some sample table data and the expected result - all as formatted text, not images (or links to images...)
– jarlh
Nov 15 '18 at 7:36
Ms access, i have added an image
– farith firdous
Nov 15 '18 at 7:39