CODEWITHSUNDEEP
mysql
Basel
Basel

Reputation: 1365

How to make more than 2 conditions in where clause?

I have three tables in MySQL Database matches,tournaments and countries. I want to have columns from those three tables which in the range of dates (between tow days) the query is :

SELECT matches.tournament_id 't_id',
       matches.localteam_ft_score,
       matches.visitorteam_ft_score,
       matches.match_time,
       matches.match_status,
       matches.match_date,
       matches.localteam_id,
       matches.visitorteam_id,
       matches.match_id,
       matches.id,
       matches.static_id,
       matches.localteam_name,
       matches.visitorteam_name,
       matches.halftime_score,
       tournaments.tournament_id,
       tournaments.league_link,
       tournaments.full_league_tr,
       countries.country_name
FROM matches
INNER JOIN tournaments ON tournaments.id = matches.tournament_id
INNER JOIN countries ON tournaments.country_id = countries.country_id
WHERE match_status IN('AET','FT','Pen.','Awarded')
  AND countries.country_name='Worldcup'
  AND matches.match_date BETWEEN '19.05.2013' AND '19.06.2013'

The problem is : I can not get the records between these two dates it is just give me the matches with '19.05.2013' date I tried many ways to solve it but nothing works . I want to know is it right to do three conditions in these way? is it the right way to get records between 2 dates?

Upvotes: 2

Views: 2893

Answers (3)

jezzipin
jezzipin

Reputation: 4254

Based upon your comment above, you need to change your data type of the match_date field to DATE (YYYY-MM-DD). MySQL cannot perform the between operation on a string. To do this you need to use the STR_TO_DATE function on the table column you wish to convert from a string to date format.

  SELECT matches.tournament_id 't_id', matches.localteam_ft_score,
   matches.visitorteam_ft_score, matches.match_time, matches.match_status,
   matches.match_date, matches.localteam_id, matches.visitorteam_id,
   matches.match_id, matches.id, matches.static_id,matches.localteam_name,
   matches.visitorteam_name, matches.halftime_score,  tournaments.tournament_id,
   tournaments.league_link, tournaments.full_league_tr, countries.country_name 
  FROM matches 
  INNER JOIN tournaments ON tournaments.id = matches.tournament_id 
  INNER JOIN countries ON tournaments.country_id = countries.country_id 
  WHERE match_status IN('AET','FT','Pen.','Awarded') 
  AND countries.country_name='Worldcup' AND STR_TO_DATE(matches.match_date,'%d.%m.%Y') between '2013.05.19' and '2013.06.19'

Upvotes: 2

Avi
Avi

Reputation: 1145

    SELECT matches.tournament_id 't_id', matches.localteam_ft_score,
    matches.visitorteam_ft_score, matches.match_time, matches.match_status,
    matches.match_date, matches.localteam_id, matches.visitorteam_id,
    matches.match_id, matches.id, matches.static_id,matches.localteam_name,
    matches.visitorteam_name, matches.halftime_score,  tournaments.tournament_id,
    tournaments.league_link, tournaments.full_league_tr, countries.country_name 
    FROM matches 
    INNER JOIN tournaments ON tournaments.id = matches.tournament_id 
    INNER JOIN countries ON tournaments.country_id = countries.country_id 
    WHERE match_status IN('AET','FT','Pen.','Awarded') 
    AND countries.country_name='Worldcup' 
AND (matches.match_date between '19.05.2013' and '19.06.2013' or matches.match_date     between 'd1'and 'd2')

Upvotes: -1

Ganesh Rengarajan
Ganesh Rengarajan

Reputation: 2006

check this one..

query1="date_format(str_to_date(date, '%d/%m/%Y'), '%m/%d/%Y') between date_format(str_to_date('"+fromdate+"', '%d/%m/%Y'), '%m/%d/%Y') and date_format(str_to_date('"+todate+"', '%d/%m/%Y'), '%m/%d/%Y')";

Upvotes: 0

Related Questions