Styjun

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;

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

share|improve this question

asked Mar 28 at 19:22

sfarzososfarzoso

9211 silver badge1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  


• 
  
  
  

  
  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 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

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

share|improve this question

asked Mar 28 at 19:22

sfarzososfarzoso

9211 silver badge1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  


• 
  
  
  

  
  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 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

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

share|improve this question

asked Mar 28 at 19:22

sfarzososfarzoso

9211 silver badge1414 bronze badges

coach_id | start | end 
 483368 2017-01-01 NULL

coach_id | name 
 483368 2017/2018

 SELECT * FROM coach_career cr
 INNER JOIN competition_seasons s
 ON SUBSTR(cr.start, 1, 4) = s.name
 WHERE cr.id = 483368

share|improve this question

asked Mar 28 at 19:22

sfarzososfarzoso

9211 silver badge1414 bronze badges

share|improve this question

asked Mar 28 at 19:22

sfarzososfarzoso

9211 silver badge1414 bronze badges

asked Mar 28 at 19:22

sfarzososfarzoso

9211 silver badge1414 bronze badges

asked Mar 28 at 19:22

sfarzososfarzoso

9211 silver badge1414 bronze badges

2 Answers
2

active

oldest

votes

1

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.

share|improve this answer

edited Mar 28 at 19:45

answered Mar 28 at 19:33

forpasforpas

45.1k66 gold badges1313 silver badges3434 bronze badges

add a comment
 | 

1

if both are string values,

SUBSTR(cr.start, 1, 4) is equal to "2017".

s.name = "2017/2018".

The 2 values are NOT equal.

share|improve this answer

answered Mar 28 at 19:28

BWSBWS

3,5221313 silver badges2323 bronze badges

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
);



);

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

Name

Email

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

Name

Email

Required, but never shown

1

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.

share|improve this answer

edited Mar 28 at 19:45

answered Mar 28 at 19:33

forpasforpas

45.1k66 gold badges1313 silver badges3434 bronze badges

add a comment
 | 

1

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.

share|improve this answer

edited Mar 28 at 19:45

answered Mar 28 at 19:33

forpasforpas

45.1k66 gold badges1313 silver badges3434 bronze badges

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.

share|improve this answer

edited Mar 28 at 19:45

answered Mar 28 at 19:33

forpasforpas

45.1k66 gold badges1313 silver badges3434 bronze badges

SELECT * FROM coach_career cr 
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', SUBSTR(cr.start, 1, 4), '%')
WHERE cr.id = 483368

SELECT * FROM coach_career cr 
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', date_format(cr.start, '%Y'), '%')
WHERE cr.id = 483368

WHERE cr.id = 483368

share|improve this answer

edited Mar 28 at 19:45

answered Mar 28 at 19:33

forpasforpas

45.1k66 gold badges1313 silver badges3434 bronze badges

answered Mar 28 at 19:33

forpasforpas

45.1k66 gold badges1313 silver badges3434 bronze badges

answered Mar 28 at 19:33

forpasforpas

45.1k66 gold badges1313 silver badges3434 bronze badges

1

if both are string values,

SUBSTR(cr.start, 1, 4) is equal to "2017".

s.name = "2017/2018".

The 2 values are NOT equal.

share|improve this answer

answered Mar 28 at 19:28

BWSBWS

3,5221313 silver badges2323 bronze badges

add a comment
 | 

1

if both are string values,

SUBSTR(cr.start, 1, 4) is equal to "2017".

s.name = "2017/2018".

The 2 values are NOT equal.

share|improve this answer

answered Mar 28 at 19:28

BWSBWS

3,5221313 silver badges2323 bronze badges

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.

share|improve this answer

answered Mar 28 at 19:28

BWSBWS

3,5221313 silver badges2323 bronze badges

share|improve this answer

answered Mar 28 at 19:28

BWSBWS

3,5221313 silver badges2323 bronze badges

answered Mar 28 at 19:28

BWSBWS

3,5221313 silver badges2323 bronze badges

answered Mar 28 at 19:28

BWSBWS

3,5221313 silver badges2323 bronze badges