两个表之间的CASE语句（高级）

Question

I wasn't sure how else to do this, I'm assuming a CASE statement but let me know if there's a better way.

I have two tables that keep track of race horses. Once keeps track of their information, the other their race results. Some of these horses are going to be bred. In order to be bred, they must be 3 years of age or older, and they must have have raced that year or the year before.

For example - Stratosphere was born in 2000, she is eligible for breeding starting 2003. However, she raced in 2002, 2003, 2004, 2005 and 2006. This means her "Open Years" are 2008, 2009, 2010, etc. etc.

I'm not sure how to make a case for this, because it would be something like (from Horses table) YOB+3, and take into account from v_testhorse (where the records are) last DATE +1 year AND that there are no foals that year (HorseID does NOT equal DamID or SireID).

My goal is to have it displayed like this - HorseName, YOB, Gender, YEARS OPEN (this is the value I'm trying to create)

Thanks in advance - let me know if you need any more info!

EDIT: Table structure- Horses: HID (ID number, unique to each horse) HName (Horse Name) YOB Gender Sire SireID Dam DamID Type OName Stable

Results:

• Options SID (ShowID, unique to each show) SName Date (as 00-00-0000) Host CName Grade Gender Year Place Tokens Earnings HName

EDIT EDIT: THe SqFiddle is http://sqlfiddle.com/#!9/5e1ad6 but if it doesn't work here's the code I used. I only entered a few values because both DB are pretty big, but here's one horse to test out!

CREATE TABLE Horses 
(HID INT(11), 
 HName VARCHAR(225), 
 YOB YEAR, 
 Sire VARCHAR(225), 
 SireID INT(5), 
 Dam VARCHAR(225), 
 DamID INT(5) );

INSERT INTO Horses (HID, HName, YOB, Sire, SireID, Dam, DamID)
VALUES ('17578','Stratosphere','2001','Stonebridge First', '464', 'Cloud Nine', '6714');
INSERT INTO Horses (HID, HName, YOB, Sire, SireID, Dam, DamID)
VALUES ('36791','Troposphere','2012','Trapper Joe', '36595', 'Stratosphere', '17578');
INSERT INTO Horses (HID, HName, YOB, Sire, SireID, Dam, DamID)
VALUES ('38249','Xalena','2011','Arlin', '31886', 'Stratosphere', '17578');


CREATE TABLE Results 
(HID INT(11), 
 HName VARCHAR(225), 
 Date2 DATE, 
 CName VARCHAR(225), 
 SName VARCHAR(225), 
 ShowID INT(5) );
 
INSERT INTO Results (HID, HName, Date2, CName, SName, ShowID)
VALUES ('17578','Stratosphere','2004-10-10','SHRA October Trotting Derby, 1 mile, 3YO Trotters', 'SHRA October Racemeet', '13');

INSERT INTO Results (HID, HName, Date2, CName, SName, ShowID)
VALUES ('17578','Stratosphere','2007-01-19','Snow Drift Trot, 3YO+ Trotting FM', 'SHRA Start the Season Meet', '889');

</div>

Answer 1

Considering two tables are named as horses and race_results something like below should pretty much do the trick.

SELECT * FROM horses
LEFT JOIN  race_results race ON (
  race.horse_id = horses.id 
  AND race.year IN (YEAR(CURDATE()), (YEAR(CURDATE()) - 1))
)
WHERE TIMESTAMPDIFF( YEAR, horses.dob, now()) >= 3;

Conside the above as a POC, and you can implement it based on your tables structure.

Update:

If you want to change your column datatype, just creat another column Date2 with the correct datatype, and then run the following query, once you cross check all the values are correct in Date2 column, you can drop the Date column.

UPDATE table_name SET Date2 = STR_TO_DATE(
  CONCAT(
    LEFT(SUBSTRING_INDEX(Date,',',1), length(Date)-2)), 
    " ",
    SUBSTRING_INDEX(Date,',',-1)
  ), '%M %d %Y'
)