LIKE doesn't return any record when used with SUBSTRSQL select join: is it possible to prefix all columns as 'prefix.*'?How can a LEFT OUTER JOIN return more records than exist in the left table?MySQL join with where clauseMySQL - UPDATE query based on SELECT QuerySQL Server: How to Join to first rowWhy query with MAX(Date) dosen't return any record?Return “null” value if no record for dateSQL query doesn't return enough recordsDuplicated field when using aliasSub-Query doesn't return expected result
Is there a list of world wide upcoming space events on the web?
"I will not" or "I don't" as an answer for negative orders?
Why would an airline put 15 passengers at once on standby?
Discrepancy regarding AoE point of origin between English and German PHB
LM324 - Issue with output in negative feedback
Smallest PRIME containing the first 11 primes as sub-strings
Where to find the Arxiv endorsement code?
Question about a degree 5 polynomial with no rational roots
My machine, client installed VPN,
How do we know neutrons have no charge?
Concerning a relationship in the team
Are the coefficients of certain product of Rogers-Ramanujan Continued Fraction non-negative?
Why do some modern glider wings like the Schleicher 29 have a tadpole shape rather than a teardrop shape?
Is population size a parameter, or sample size a statistic?
Why does my browser attempt to download pages from http://clhs.lisp.se instead of viewing them normally?
Selection Sort Algorithm (Python)
How do my husband and I get over our fear of having another difficult baby?
What happens to a net with the Returning Weapon artificer infusion after it hits?
Speed and Velocity in Russian
Why is STARTTLS still used?
How to prevent pickpocketing in busy bars?
Is determiner 'a' needed here?
Maximize assigned tasks to each worker
Top off gas with old oil, is that bad?
LIKE doesn't return any record when used with SUBSTR
SQL select join: is it possible to prefix all columns as 'prefix.*'?How can a LEFT OUTER JOIN return more records than exist in the left table?MySQL join with where clauseMySQL - UPDATE query based on SELECT QuerySQL Server: How to Join to first rowWhy query with MAX(Date) dosen't return any record?Return “null” value if no record for dateSQL query doesn't return enough recordsDuplicated field when using aliasSub-Query doesn't return expected result
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I have two tables:
coach_career
coach_id | start | end
483368 2017-01-01 NULL
competition_seasons
coach_id | name
483368 2017/2018
I have wrote query:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON SUBSTR(cr.start, 1, 4) = s.name
WHERE cr.id = 483368
Essentially I need to return the coach which have as start
value the year of name
field available in competition_season
, I used SUBSTR
to extract only the year, the problem's that this query return no records.
mysql sql
|
show 2 more comments
I have two tables:
coach_career
coach_id | start | end
483368 2017-01-01 NULL
competition_seasons
coach_id | name
483368 2017/2018
I have wrote query:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON SUBSTR(cr.start, 1, 4) = s.name
WHERE cr.id = 483368
Essentially I need to return the coach which have as start
value the year of name
field available in competition_season
, I used SUBSTR
to extract only the year, the problem's that this query return no records.
mysql sql
Have you triedSELECT SUBSTR(cr.start, 1, 4) FROM coach_career
andSELECT name FROM competition_seasons
to see if there are any matching values?
– mtr.web
Mar 28 at 19:25
If I remove the SUBSTR the record is returned correctly, should be a mistake on SUBSTR
– sfarzoso
Mar 28 at 19:26
2
This is a date, so don't substring on it. Instead extract the year usingON Year(cr.Start) = s.name
Unless you're storing your dates as varchar. In which case... YUCK.
– JNevill
Mar 28 at 19:27
@JNevill You're right, I completely overlooked it, thanks..
– sfarzoso
Mar 28 at 19:28
Also.. your name value is2017/2018
. You have two values in a single column. You are going to have a bad time here. Perhaps something likeON s.name LIKE CONCAT('%', YEAR(cr.start), '%')
that's not going to be quick though. Ultimately you'd be better off storing two records in this table (one for each year/name).
– JNevill
Mar 28 at 19:29
|
show 2 more comments
I have two tables:
coach_career
coach_id | start | end
483368 2017-01-01 NULL
competition_seasons
coach_id | name
483368 2017/2018
I have wrote query:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON SUBSTR(cr.start, 1, 4) = s.name
WHERE cr.id = 483368
Essentially I need to return the coach which have as start
value the year of name
field available in competition_season
, I used SUBSTR
to extract only the year, the problem's that this query return no records.
mysql sql
I have two tables:
coach_career
coach_id | start | end
483368 2017-01-01 NULL
competition_seasons
coach_id | name
483368 2017/2018
I have wrote query:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON SUBSTR(cr.start, 1, 4) = s.name
WHERE cr.id = 483368
Essentially I need to return the coach which have as start
value the year of name
field available in competition_season
, I used SUBSTR
to extract only the year, the problem's that this query return no records.
mysql sql
mysql sql
asked Mar 28 at 19:22
sfarzososfarzoso
921 silver badge14 bronze badges
921 silver badge14 bronze badges
Have you triedSELECT SUBSTR(cr.start, 1, 4) FROM coach_career
andSELECT name FROM competition_seasons
to see if there are any matching values?
– mtr.web
Mar 28 at 19:25
If I remove the SUBSTR the record is returned correctly, should be a mistake on SUBSTR
– sfarzoso
Mar 28 at 19:26
2
This is a date, so don't substring on it. Instead extract the year usingON Year(cr.Start) = s.name
Unless you're storing your dates as varchar. In which case... YUCK.
– JNevill
Mar 28 at 19:27
@JNevill You're right, I completely overlooked it, thanks..
– sfarzoso
Mar 28 at 19:28
Also.. your name value is2017/2018
. You have two values in a single column. You are going to have a bad time here. Perhaps something likeON s.name LIKE CONCAT('%', YEAR(cr.start), '%')
that's not going to be quick though. Ultimately you'd be better off storing two records in this table (one for each year/name).
– JNevill
Mar 28 at 19:29
|
show 2 more comments
Have you triedSELECT SUBSTR(cr.start, 1, 4) FROM coach_career
andSELECT name FROM competition_seasons
to see if there are any matching values?
– mtr.web
Mar 28 at 19:25
If I remove the SUBSTR the record is returned correctly, should be a mistake on SUBSTR
– sfarzoso
Mar 28 at 19:26
2
This is a date, so don't substring on it. Instead extract the year usingON Year(cr.Start) = s.name
Unless you're storing your dates as varchar. In which case... YUCK.
– JNevill
Mar 28 at 19:27
@JNevill You're right, I completely overlooked it, thanks..
– sfarzoso
Mar 28 at 19:28
Also.. your name value is2017/2018
. You have two values in a single column. You are going to have a bad time here. Perhaps something likeON s.name LIKE CONCAT('%', YEAR(cr.start), '%')
that's not going to be quick though. Ultimately you'd be better off storing two records in this table (one for each year/name).
– JNevill
Mar 28 at 19:29
Have you tried
SELECT SUBSTR(cr.start, 1, 4) FROM coach_career
and SELECT name FROM competition_seasons
to see if there are any matching values?– mtr.web
Mar 28 at 19:25
Have you tried
SELECT SUBSTR(cr.start, 1, 4) FROM coach_career
and SELECT name FROM competition_seasons
to see if there are any matching values?– mtr.web
Mar 28 at 19:25
If I remove the SUBSTR the record is returned correctly, should be a mistake on SUBSTR
– sfarzoso
Mar 28 at 19:26
If I remove the SUBSTR the record is returned correctly, should be a mistake on SUBSTR
– sfarzoso
Mar 28 at 19:26
2
2
This is a date, so don't substring on it. Instead extract the year using
ON Year(cr.Start) = s.name
Unless you're storing your dates as varchar. In which case... YUCK.– JNevill
Mar 28 at 19:27
This is a date, so don't substring on it. Instead extract the year using
ON Year(cr.Start) = s.name
Unless you're storing your dates as varchar. In which case... YUCK.– JNevill
Mar 28 at 19:27
@JNevill You're right, I completely overlooked it, thanks..
– sfarzoso
Mar 28 at 19:28
@JNevill You're right, I completely overlooked it, thanks..
– sfarzoso
Mar 28 at 19:28
Also.. your name value is
2017/2018
. You have two values in a single column. You are going to have a bad time here. Perhaps something like ON s.name LIKE CONCAT('%', YEAR(cr.start), '%')
that's not going to be quick though. Ultimately you'd be better off storing two records in this table (one for each year/name).– JNevill
Mar 28 at 19:29
Also.. your name value is
2017/2018
. You have two values in a single column. You are going to have a bad time here. Perhaps something like ON s.name LIKE CONCAT('%', YEAR(cr.start), '%')
that's not going to be quick though. Ultimately you'd be better off storing two records in this table (one for each year/name).– JNevill
Mar 28 at 19:29
|
show 2 more comments
2 Answers
2
active
oldest
votes
This:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', SUBSTR(cr.start, 1, 4), '%')
WHERE cr.id = 483368
will join the tables when name
contains the value of start
.
If start
's data type is date
then use date_format()
to extract the year like this:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', date_format(cr.start, '%Y'), '%')
WHERE cr.id = 483368
Are you sure about using the condition:
WHERE cr.id = 483368
It does not seem right.
add a comment
|
if both are string values,
SUBSTR(cr.start, 1, 4) is equal to "2017".
s.name = "2017/2018".
The 2 values are NOT equal.
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/4.0/"u003ecc by-sa 4.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%2f55405414%2flike-doesnt-return-any-record-when-used-with-substr%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
This:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', SUBSTR(cr.start, 1, 4), '%')
WHERE cr.id = 483368
will join the tables when name
contains the value of start
.
If start
's data type is date
then use date_format()
to extract the year like this:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', date_format(cr.start, '%Y'), '%')
WHERE cr.id = 483368
Are you sure about using the condition:
WHERE cr.id = 483368
It does not seem right.
add a comment
|
This:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', SUBSTR(cr.start, 1, 4), '%')
WHERE cr.id = 483368
will join the tables when name
contains the value of start
.
If start
's data type is date
then use date_format()
to extract the year like this:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', date_format(cr.start, '%Y'), '%')
WHERE cr.id = 483368
Are you sure about using the condition:
WHERE cr.id = 483368
It does not seem right.
add a comment
|
This:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', SUBSTR(cr.start, 1, 4), '%')
WHERE cr.id = 483368
will join the tables when name
contains the value of start
.
If start
's data type is date
then use date_format()
to extract the year like this:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', date_format(cr.start, '%Y'), '%')
WHERE cr.id = 483368
Are you sure about using the condition:
WHERE cr.id = 483368
It does not seem right.
This:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', SUBSTR(cr.start, 1, 4), '%')
WHERE cr.id = 483368
will join the tables when name
contains the value of start
.
If start
's data type is date
then use date_format()
to extract the year like this:
SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', date_format(cr.start, '%Y'), '%')
WHERE cr.id = 483368
Are you sure about using the condition:
WHERE cr.id = 483368
It does not seem right.
edited Mar 28 at 19:45
answered Mar 28 at 19:33
forpasforpas
45.1k6 gold badges13 silver badges34 bronze badges
45.1k6 gold badges13 silver badges34 bronze badges
add a comment
|
add a comment
|
if both are string values,
SUBSTR(cr.start, 1, 4) is equal to "2017".
s.name = "2017/2018".
The 2 values are NOT equal.
add a comment
|
if both are string values,
SUBSTR(cr.start, 1, 4) is equal to "2017".
s.name = "2017/2018".
The 2 values are NOT equal.
add a comment
|
if both are string values,
SUBSTR(cr.start, 1, 4) is equal to "2017".
s.name = "2017/2018".
The 2 values are NOT equal.
if both are string values,
SUBSTR(cr.start, 1, 4) is equal to "2017".
s.name = "2017/2018".
The 2 values are NOT equal.
answered Mar 28 at 19:28
BWSBWS
3,52213 silver badges23 bronze badges
3,52213 silver badges23 bronze badges
add a comment
|
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%2f55405414%2flike-doesnt-return-any-record-when-used-with-substr%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
Have you tried
SELECT SUBSTR(cr.start, 1, 4) FROM coach_career
andSELECT name FROM competition_seasons
to see if there are any matching values?– mtr.web
Mar 28 at 19:25
If I remove the SUBSTR the record is returned correctly, should be a mistake on SUBSTR
– sfarzoso
Mar 28 at 19:26
2
This is a date, so don't substring on it. Instead extract the year using
ON Year(cr.Start) = s.name
Unless you're storing your dates as varchar. In which case... YUCK.– JNevill
Mar 28 at 19:27
@JNevill You're right, I completely overlooked it, thanks..
– sfarzoso
Mar 28 at 19:28
Also.. your name value is
2017/2018
. You have two values in a single column. You are going to have a bad time here. Perhaps something likeON s.name LIKE CONCAT('%', YEAR(cr.start), '%')
that's not going to be quick though. Ultimately you'd be better off storing two records in this table (one for each year/name).– JNevill
Mar 28 at 19:29