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
Originally posted by bee_girl
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
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...