CODEWITHSUNDEEP
mysqlsql
Jidic
Jidic

Reputation: 147

Cannot compare year with different table fields

I need to extract a coach that have a career that start from a specific year for a specific team.id, these are the data sample:

competition_seasons

| id | competition_id | season_id | name 
  22         48            14214    2017/2018

coach_career

| coach_id | team_id | start        | end
   223496      69       2018-07-01    NULL
   223496     4345      2011-10-01    2015-06-01
   223496    15376      2011-02-01    2011-10-01

In this case I need to extract the career of the coach that start in the year 2018 contained in season.name (2017/2018), of the team.id = 69.

For achieve this, I tried this query:

SELECT *
FROM coach_career cr
INNER JOIN coach c ON c.id = cr.coach_id
INNER JOIN competition_seasons s ON s.id = 22
WHERE cr.team_id = 69
AND `start` LIKE concat('%', cast(extract(year from s.name) as char(100)), '%')

but this will return an empty result, I guess I commit some mistake on year extraction, someone know how to achieve this?

NB: The table coach is the master table of coach_career, and simply contains the information of the person.

Upvotes: 1

Views: 34

Answers (2)

D-Shih
D-Shih

Reputation: 46229

You can try this query.

Because you wnat to use colnum with like, so || instead of concat.

then move startLIKE '%' || s.name || '%' condition to ON of JOIN, because INNER JOIN competition_seasons s ON s.id = 22 will return all play.

SELECT *
FROM coach_career cr
INNER JOIN coach c ON c.id = cr.coach_id
INNER JOIN competition_seasons s ON `start` LIKE '%' || s.name || '%'
WHERE  
  s.id = 22 
and 
  cr.team_id = 69

sqlfiddle

[Results]:

| coach_id | team_id |      start |    end | id | competition_id | season_id |      name |
|----------|---------|------------|--------|----|----------------|-----------|-----------|
|   223496 |      69 | 2018-07-01 | (null) | 22 |             48 |     14214 | 2017/2018 |

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37367

You could try below code. It uses locate() function to check wether year of starting career is included within name of a season.

Sample data:

create table competition_seasons(id int, competition_id int, season_id int, name varchar(10));
insert into competition_seasons values (22, 48, 14214, '2017/2018');
create table coach_career(coach_id int, team_id int, `start` date, `end` date);
insert into coach_career values
(223496, 69, '2018-07-01', NULL),
(223496, 4345, '2011-10-01', '2015-06-01'),
(223496, 15376, '2011-02-01', '2011-10-01');

T-SQL:

select * from coach_career cc
where exists(select 1 from competition_seasons
             where locate(year(cc.start), name) > 0);

Upvotes: 1

Related Questions