Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: Majore problem with max()

    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!

  2. #2
    Join Date
    Apr 2004
    Posts
    3

    PS

    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)

  3. #3
    Join Date
    Apr 2004
    Posts
    1

    Re: Majore problem with max()

    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










    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

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •