How to join 2 tables in mysql but with different values in one of the tables
I have 2 tables(as shown by the image below) inbox_messages_table and users_table
So what i am trying to do is combining the 2 tables ON user_id but in my inbox_messages_table i have an id which i created for the website itself which is (-1) to have it unique so how can i do this and be able to retrieve the -1 too with others ?
my query so far is this :
// $user_id is the logged in user
SELECT * FROM inbox_messages_table i JOIN users_table u ON i.from_user_id = u.user_id WHERE u.banned = 0 AND to_user_id = $user_id
AND deleted = 0 ORDER BY message_timestamp DESC
mysql php
asked 8 hours ago
firasheloufirashelou
132
New contributor
firashelou is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I have 2 tables(as shown by the image below) inbox_messages_table and users_table
So what i am trying to do is combining the 2 tables ON user_id but in my inbox_messages_table i have an id which i created for the website itself which is (-1) to have it unique so how can i do this and be able to retrieve the -1 too with others ?
my query so far is this :
// $user_id is the logged in user
SELECT * FROM inbox_messages_table i JOIN users_table u ON i.from_user_id = u.user_id WHERE u.banned = 0 AND to_user_id = $user_id
AND deleted = 0 ORDER BY message_timestamp DESC
mysql php
asked 8 hours ago
firasheloufirashelou
132
New contributor
firashelou is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Please give the output ofSHOW CREATE TABLE my_tableG;for your tables and put in someINSERT INTO my_table VALUES(...);statements. Have a look at my profile for tips on how to ask questions - use a fiddle at dbfiddle.uk or the other sites mentioned in the posts in my profile.
– Vérace
7 hours ago
add a comment |
I have 2 tables(as shown by the image below) inbox_messages_table and users_table
So what i am trying to do is combining the 2 tables ON user_id but in my inbox_messages_table i have an id which i created for the website itself which is (-1) to have it unique so how can i do this and be able to retrieve the -1 too with others ?
my query so far is this :
// $user_id is the logged in user
SELECT * FROM inbox_messages_table i JOIN users_table u ON i.from_user_id = u.user_id WHERE u.banned = 0 AND to_user_id = $user_id
AND deleted = 0 ORDER BY message_timestamp DESC
mysql php
asked 8 hours ago
firasheloufirashelou
132
New contributor
firashelou is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have 2 tables(as shown by the image below) inbox_messages_table and users_table
So what i am trying to do is combining the 2 tables ON user_id but in my inbox_messages_table i have an id which i created for the website itself which is (-1) to have it unique so how can i do this and be able to retrieve the -1 too with others ?
my query so far is this :
// $user_id is the logged in user
SELECT * FROM inbox_messages_table i JOIN users_table u ON i.from_user_id = u.user_id WHERE u.banned = 0 AND to_user_id = $user_id
AND deleted = 0 ORDER BY message_timestamp DESC
mysql php
mysql php
asked 8 hours ago
firasheloufirashelou
132
New contributor
firashelou is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 hours ago
firasheloufirashelou
132
New contributor
firashelou is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 hours ago
firasheloufirashelou
132
New contributor
firashelou is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 hours ago
firasheloufirashelou
132
asked 8 hours ago
firasheloufirashelou
132
132
New contributor
firashelou is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
firashelou is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
firashelou is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Please give the output ofSHOW CREATE TABLE my_tableG;for your tables and put in someINSERT INTO my_table VALUES(...);statements. Have a look at my profile for tips on how to ask questions - use a fiddle at dbfiddle.uk or the other sites mentioned in the posts in my profile.
– Vérace
7 hours ago
add a comment |
Please give the output ofSHOW CREATE TABLE my_tableG;for your tables and put in someINSERT INTO my_table VALUES(...);statements. Have a look at my profile for tips on how to ask questions - use a fiddle at dbfiddle.uk or the other sites mentioned in the posts in my profile.
– Vérace
7 hours ago
Please give the output of
SHOW CREATE TABLE my_tableG; for your tables and put in some INSERT INTO my_table VALUES(...); statements. Have a look at my profile for tips on how to ask questions - use a fiddle at dbfiddle.uk or the other sites mentioned in the posts in my profile.– Vérace
7 hours ago
Please give the output of
SHOW CREATE TABLE my_tableG; for your tables and put in some INSERT INTO my_table VALUES(...); statements. Have a look at my profile for tips on how to ask questions - use a fiddle at dbfiddle.uk or the other sites mentioned in the posts in my profile.– Vérace
7 hours ago
add a comment |
1 Answer
1
active
oldest
votes
You can use LEFT JOIN instead of JOIN.
With LEFT JOIN the query will return all records from the left table i.e. the one in the FROM, even if there is no matching record in the joint table.
SELECT * FROM inbox_messages_table i
LEFT JOIN users_table u ON i.from_user_id = u.user_id
WHERE u.banned = 0 AND to_user_id = $user_id AND deleted = 0 ORDER BY message_timestamp DESC
answered 7 hours ago
FloTFloT
19617
1
thanks a lot ! it worked ! i had to remove u.banned = 0 too
– firashelou
7 hours ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
firashelou is a new contributor. Be nice, and check out our Code of Conduct.
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%2fdba.stackexchange.com%2fquestions%2f231136%2fhow-to-join-2-tables-in-mysql-but-with-different-values-in-one-of-the-tables%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
You can use LEFT JOIN instead of JOIN.
With LEFT JOIN the query will return all records from the left table i.e. the one in the FROM, even if there is no matching record in the joint table.
SELECT * FROM inbox_messages_table i
LEFT JOIN users_table u ON i.from_user_id = u.user_id
WHERE u.banned = 0 AND to_user_id = $user_id AND deleted = 0 ORDER BY message_timestamp DESC
answered 7 hours ago
FloTFloT
19617
1
thanks a lot ! it worked ! i had to remove u.banned = 0 too
– firashelou
7 hours ago
add a comment |
You can use LEFT JOIN instead of JOIN.
With LEFT JOIN the query will return all records from the left table i.e. the one in the FROM, even if there is no matching record in the joint table.
SELECT * FROM inbox_messages_table i
LEFT JOIN users_table u ON i.from_user_id = u.user_id
WHERE u.banned = 0 AND to_user_id = $user_id AND deleted = 0 ORDER BY message_timestamp DESC
answered 7 hours ago
FloTFloT
19617
1
thanks a lot ! it worked ! i had to remove u.banned = 0 too
– firashelou
7 hours ago
add a comment |
You can use LEFT JOIN instead of JOIN.
With LEFT JOIN the query will return all records from the left table i.e. the one in the FROM, even if there is no matching record in the joint table.
SELECT * FROM inbox_messages_table i
LEFT JOIN users_table u ON i.from_user_id = u.user_id
WHERE u.banned = 0 AND to_user_id = $user_id AND deleted = 0 ORDER BY message_timestamp DESC
answered 7 hours ago
FloTFloT
19617
You can use LEFT JOIN instead of JOIN.
With LEFT JOIN the query will return all records from the left table i.e. the one in the FROM, even if there is no matching record in the joint table.
SELECT * FROM inbox_messages_table i
LEFT JOIN users_table u ON i.from_user_id = u.user_id
WHERE u.banned = 0 AND to_user_id = $user_id AND deleted = 0 ORDER BY message_timestamp DESC
answered 7 hours ago
FloTFloT
19617
edited 3 hours ago
answered 7 hours ago
FloTFloT
19617
answered 7 hours ago
FloTFloT
19617
answered 7 hours ago
FloTFloT
19617
19617
1
thanks a lot ! it worked ! i had to remove u.banned = 0 too
– firashelou
7 hours ago
add a comment |
1
thanks a lot ! it worked ! i had to remove u.banned = 0 too
– firashelou
7 hours ago
1
1
thanks a lot ! it worked ! i had to remove u.banned = 0 too
– firashelou
7 hours ago
thanks a lot ! it worked ! i had to remove u.banned = 0 too
– firashelou
7 hours ago
add a comment |
firashelou is a new contributor. Be nice, and check out our Code of Conduct.
firashelou is a new contributor. Be nice, and check out our Code of Conduct.
firashelou is a new contributor. Be nice, and check out our Code of Conduct.
firashelou is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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.
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%2fdba.stackexchange.com%2fquestions%2f231136%2fhow-to-join-2-tables-in-mysql-but-with-different-values-in-one-of-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
Please give the output of
SHOW CREATE TABLE my_tableG;for your tables and put in someINSERT INTO my_table VALUES(...);statements. Have a look at my profile for tips on how to ask questions - use a fiddle at dbfiddle.uk or the other sites mentioned in the posts in my profile.– Vérace
7 hours ago