Hi, I'm going crasy over a query, and hoping that anyone can help me.

Im using MySql 4.0.18, and is using 2 tables

Person
-First name
-Last name
-Birthdate
-Sex
-Occupation

and

Visit
-id
-Visitdate
-First name
-Last name
-Birthdate
-VisitTo

In my program, I have to be able to seek up persons, and mabye come up with many different persons.

These persons can visit many times, but I only want to know all the information in visit on the last visit plus all the information in person.

Say that Phil Guy has visited me 2 times then the information in visit is:

+id+Visitdate +Firstname+Lastname+Birthdate +VisitTo+
| 1 | 2004-01-01| Phil | Guy | 1975-02-03 | Me |
| 2 | 2003-03-04| Phil | Guy | 1975-02-03 | You |

Then I want to only know about the first row, and all about him from person.

Have tried many things, and twisting and turning bouth my head, and the max query...

Please help me!

By the way,

I'm using INNODB, and Firstname, Lastname and Birthdate is foreign keys in Visit and primary keys in person.

id in Visit is primary key.

(I know that it's not sutch a good idea to use this primary key in person, but i must use it anyway)

I am giving you a query, but i am not 100% sure about it but give it a try



select v.id, v.visitdate, p.firstname,p.lastname,p.birthdate, vivisitto from person p, visit v where v.firstname=p.firstname and v.lastname = p.lastname and v.birthdate = p.birthdate and NOT EXISTS ( select * from visit v1 where v.firstname=v1.firstname and v.lastname=v1.lastname and v.birthdate = v1.birthdate and v.visitdate<v1.visitdate);


What i am trying to do is joining both the tables and making sure that there is no other visit row with same fname, lname and birthdate but which has a more recent date..

I hope this helps

Sri