Hello to all,
Well I have a little dilemma. I am trying to filter records that need to be delete from two different tables based if the person is a certain age. I do not store the age anywhere except in my offense table to record the age at which an offense was committed. However, I store their dob in the persons table and calculate the age using the following join query using persons to calculate the age and offense to associate the records with that person record. However, I only want to see any person that is older 17 or older. When I attempt to query only the persons table to calculate the age without the join and then select age >=17 it tells me there is no column named Age and I figure because it is a pseudo table. When I attempt to limit the return on the query with the join it does not give me that error because offense table has an age field, but it gives me unwanted results like below.
I only want the records from persons and matching records from offense where the calculated age is >=17.
mysql> select I.PID as PID,I.name as name,YEAR(CURRENT_DATE()) - YEAR(I.dob)-(RI
GHT(CURRENT_DATE(),5)<RIGHT(I.dob,5)) as Age, C.offenseID as OffenseID,C.offense
as offense from persons as I, offense as C where I.PID=C.PID;
+-----+------------------+------+-----------+----------+
| PID | name | Age | OffenseID | offense |
+-----+------------------+------+-----------+----------+
| 1 | Ricardo Enriquez | 16 | 1 | Grafitti |
| 2 | Rodney Castaneda | 36 | 2 | Theft |
| 3 | Rodney Castaneda | 36 | 3 | Theft |
+-----+------------------+------+-----------+----------+
3 rows in set (0.00 sec)
mysql>
Any feedback is appreciated.
Chapa
