SQL query, check year for one column
I have a column in my MySQL table showing the year. Example:
Table: Information ID | Year_sold --- -------- 1 | 2002-2010 2 | 2005-2015 3 | 2011 -____ 4 | 1975-1978
I'll ask a table if it has data to display for a specific year. Example:
SELECT * FROM Information WHERE Year_sold = '2012';
It won't work of course, but how can I enter the SQL query if the result is IDs 2 and 3.
If the item is still active and for sale, the years will be shown as ID 3, "2011 -____". I can replace "____" if necessary.
source to share
Use the functions BETWEEN
, LEFT
and SUBSTRING_INDEX
.
SELECT ID, Year_sold
FROM Information
WHERE '2012' BETWEEN LEFT(Year_sold,4) AND SUBSTRING_INDEX(Year_sold,'-',-1)
Output
ID Year_sold
2 2005-2015
3 2011-____
SQL Fiddle: http://sqlfiddle.com/#!9/7df7b7/1/0
If possible, I would start again with your table structure and have something like:
Table: Information
ID | Year_sold_from | Year_sold_to
--- ------------------------------
1 | 2002 | 2010
2 | 2005 | 2015
3 | 2011 | null
4 | 1975 | 1978
source to share