SQLite: How to JOIN with a partial column match?
I'm trying to get all the data from the Documents table where the list of Box numbers in the BoxNumber table is somewhere within a string in a column in the Documents table.
The problem I'm running into right now is that none of the example code with '%' + ColumnName + '%' or '%' || ColumnName || '%' will work. it'll just return either nothing, or all the data in the Documents table.
Example code:
This returns all data in the Documents table instead of all data containing a document number similar to the list in the BoxNumbers table.
SELECT *
from [Documents]
LEFT JOIN BoxNumbers ON
BoxNumbers.BoxNum like '%' + Documents.DocNum + '%'
Anyone know why this isn't working? It seems like it's worked for people using MySQL and SQL server, so is this just another quirk of SQLite?
sql sqlite join left-join
edited Nov 15 '18 at 22:59
Rex CoolCode Charles
7731821
asked Nov 15 '18 at 22:15
Andrew CarrAndrew Carr
12
add a comment |
I'm trying to get all the data from the Documents table where the list of Box numbers in the BoxNumber table is somewhere within a string in a column in the Documents table.
The problem I'm running into right now is that none of the example code with '%' + ColumnName + '%' or '%' || ColumnName || '%' will work. it'll just return either nothing, or all the data in the Documents table.
Example code:
This returns all data in the Documents table instead of all data containing a document number similar to the list in the BoxNumbers table.
SELECT *
from [Documents]
LEFT JOIN BoxNumbers ON
BoxNumbers.BoxNum like '%' + Documents.DocNum + '%'
Anyone know why this isn't working? It seems like it's worked for people using MySQL and SQL server, so is this just another quirk of SQLite?
sql sqlite join left-join
edited Nov 15 '18 at 22:59
Rex CoolCode Charles
7731821
asked Nov 15 '18 at 22:15
Andrew CarrAndrew Carr
12
use join not left join then you return only rows that match
– Hogan
Nov 15 '18 at 22:29
This returned the same result. I ended up just pulling the data I needed from one column, putting it in a new column, and matching based on that.
– Andrew Carr
Nov 16 '18 at 2:02
add a comment |
I'm trying to get all the data from the Documents table where the list of Box numbers in the BoxNumber table is somewhere within a string in a column in the Documents table.
The problem I'm running into right now is that none of the example code with '%' + ColumnName + '%' or '%' || ColumnName || '%' will work. it'll just return either nothing, or all the data in the Documents table.
Example code:
This returns all data in the Documents table instead of all data containing a document number similar to the list in the BoxNumbers table.
SELECT *
from [Documents]
LEFT JOIN BoxNumbers ON
BoxNumbers.BoxNum like '%' + Documents.DocNum + '%'
Anyone know why this isn't working? It seems like it's worked for people using MySQL and SQL server, so is this just another quirk of SQLite?
sql sqlite join left-join
edited Nov 15 '18 at 22:59
Rex CoolCode Charles
7731821
asked Nov 15 '18 at 22:15
Andrew CarrAndrew Carr
12
I'm trying to get all the data from the Documents table where the list of Box numbers in the BoxNumber table is somewhere within a string in a column in the Documents table.
The problem I'm running into right now is that none of the example code with '%' + ColumnName + '%' or '%' || ColumnName || '%' will work. it'll just return either nothing, or all the data in the Documents table.
Example code:
This returns all data in the Documents table instead of all data containing a document number similar to the list in the BoxNumbers table.
SELECT *
from [Documents]
LEFT JOIN BoxNumbers ON
BoxNumbers.BoxNum like '%' + Documents.DocNum + '%'
Anyone know why this isn't working? It seems like it's worked for people using MySQL and SQL server, so is this just another quirk of SQLite?
sql sqlite join left-join
sql sqlite join left-join
edited Nov 15 '18 at 22:59
Rex CoolCode Charles
7731821
asked Nov 15 '18 at 22:15
Andrew CarrAndrew Carr
12
edited Nov 15 '18 at 22:59
Rex CoolCode Charles
7731821
asked Nov 15 '18 at 22:15
Andrew CarrAndrew Carr
12
edited Nov 15 '18 at 22:59
Rex CoolCode Charles
7731821
edited Nov 15 '18 at 22:59
Rex CoolCode Charles
7731821
edited Nov 15 '18 at 22:59
Rex CoolCode Charles
7731821
7731821
asked Nov 15 '18 at 22:15
Andrew CarrAndrew Carr
12
asked Nov 15 '18 at 22:15
Andrew CarrAndrew Carr
12
asked Nov 15 '18 at 22:15
Andrew CarrAndrew Carr
12
12
use join not left join then you return only rows that match
– Hogan
Nov 15 '18 at 22:29
This returned the same result. I ended up just pulling the data I needed from one column, putting it in a new column, and matching based on that.
– Andrew Carr
Nov 16 '18 at 2:02
add a comment |
use join not left join then you return only rows that match
– Hogan
Nov 15 '18 at 22:29
This returned the same result. I ended up just pulling the data I needed from one column, putting it in a new column, and matching based on that.
– Andrew Carr
Nov 16 '18 at 2:02
use join not left join then you return only rows that match
– Hogan
Nov 15 '18 at 22:29
use join not left join then you return only rows that match
– Hogan
Nov 15 '18 at 22:29
This returned the same result. I ended up just pulling the data I needed from one column, putting it in a new column, and matching based on that.
– Andrew Carr
Nov 16 '18 at 2:02
This returned the same result. I ended up just pulling the data I needed from one column, putting it in a new column, and matching based on that.
– Andrew Carr
Nov 16 '18 at 2:02
add a comment |
1 Answer
1
active
oldest
votes
The SQLite concatenation operator is not + but ||, so change to this:
SELECT *
from [Documents]
INNER JOIN BoxNumbers ON
BoxNumbers.BoxNum like '%' || Documents.DocNum || '%'
with INNER join.
answered Nov 15 '18 at 22:23
forpasforpas
9,8421421
Yeah, I've tried that too and it gives me the same result.
– Andrew Carr
Nov 15 '18 at 22:24
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
});
}
});
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%2f53328658%2fsqlite-how-to-join-with-a-partial-column-match%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
The SQLite concatenation operator is not + but ||, so change to this:
SELECT *
from [Documents]
INNER JOIN BoxNumbers ON
BoxNumbers.BoxNum like '%' || Documents.DocNum || '%'
with INNER join.
answered Nov 15 '18 at 22:23
forpasforpas
9,8421421
Yeah, I've tried that too and it gives me the same result.
– Andrew Carr
Nov 15 '18 at 22:24
add a comment |
The SQLite concatenation operator is not + but ||, so change to this:
SELECT *
from [Documents]
INNER JOIN BoxNumbers ON
BoxNumbers.BoxNum like '%' || Documents.DocNum || '%'
with INNER join.
answered Nov 15 '18 at 22:23
forpasforpas
9,8421421
Yeah, I've tried that too and it gives me the same result.
– Andrew Carr
Nov 15 '18 at 22:24
add a comment |
The SQLite concatenation operator is not + but ||, so change to this:
SELECT *
from [Documents]
INNER JOIN BoxNumbers ON
BoxNumbers.BoxNum like '%' || Documents.DocNum || '%'
with INNER join.
answered Nov 15 '18 at 22:23
forpasforpas
9,8421421
The SQLite concatenation operator is not + but ||, so change to this:
SELECT *
from [Documents]
INNER JOIN BoxNumbers ON
BoxNumbers.BoxNum like '%' || Documents.DocNum || '%'
with INNER join.
answered Nov 15 '18 at 22:23
forpasforpas
9,8421421
edited Nov 15 '18 at 22:27
answered Nov 15 '18 at 22:23
forpasforpas
9,8421421
answered Nov 15 '18 at 22:23
forpasforpas
9,8421421
answered Nov 15 '18 at 22:23
forpasforpas
9,8421421
9,8421421
Yeah, I've tried that too and it gives me the same result.
– Andrew Carr
Nov 15 '18 at 22:24
add a comment |
Yeah, I've tried that too and it gives me the same result.
– Andrew Carr
Nov 15 '18 at 22:24
Yeah, I've tried that too and it gives me the same result.
– Andrew Carr
Nov 15 '18 at 22:24
Yeah, I've tried that too and it gives me the same result.
– Andrew Carr
Nov 15 '18 at 22:24
add a comment |
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.
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%2f53328658%2fsqlite-how-to-join-with-a-partial-column-match%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
use join not left join then you return only rows that match
– Hogan
Nov 15 '18 at 22:29
This returned the same result. I ended up just pulling the data I needed from one column, putting it in a new column, and matching based on that.
– Andrew Carr
Nov 16 '18 at 2:02